Sub LabtoCell(rng)
For Each cel In rng
CIELABtoXYZ cel.Offset(, -5), cel.Offset(, -4), cel.Offset(, -3)
XYZtoRGB X, Y, Z
Debug.Print X, Y, Z, R, G, b
cel.Interior.Color = RGB(R, G, b)
Next
End Sub
call like LabtoCell Range("g11:g15")
change the cel offsets to suit the column being changed, or pass an additional parameter, the colours looked like only 2 different colours so i printed the values, as below, to make sure i was not getting any values that were not changing
Last edited by westconn1; Jan 13th, 2018 at 06:48 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
just add the sub as posted to your existing code module, you can call it from any other procedure, or a menu button, as you need to somehow specify the range of cells to update, you could use an inputbox to get the user to input the range
if you want to colour the column E the offsets should be -3, -2, -1, as i coloured column g the offsets were 2 more (negative)
i edited the code a bit to make it easy to adjust the column, using an additional parameter
Code:
Sub LabtoCell(rng, col)
For Each cel In rng
CIELABtoXYZ cel.Offset(, col), cel.Offset(, col + 1), cel.Offset(, col + 2)
XYZtoRGB X, Y, Z
Debug.Print X, Y, Z, R, G, b
cel.Interior.Color = RGB(R, G, b)
Next
End Sub
to match the original sample value for col would be -5 (no of columns to first column of colour data), for column E col would be -3, or to colour column A col would be 1, for existing data columns, call like
Code:
LabtoCell range("E11:E15", -3)
as it was a bit difficult to guess how you actually wanted to implement the code, i tried to make it universal, not specific to any range of columns, if your use of columns is constant the code in the first sample may be enough
note i did not in any way test if the results from the calculations were correct, i just made a procedure to apply the existing calculations to a generic range of cells on the same row as the input data
Last edited by westconn1; Jan 13th, 2018 at 05:22 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Sorry, but I repeat "I have not many knownledge of VB".
i am not sure that i understand what you are having problem with, you have the required code, which is tested and works as requested
i will request this thread is moved to the correct forum, someone else there may be able to give you more assistance
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
you still have not specified how you want to start the code
do you want to click a button or what?
i can not demonstrate a ribbon button as my excel is pre-ribbon
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
the code i posted in #2 will change the colour of all the cells in the specified range, from one button (or whatever you want to initiate the action)
Code:
Sub LabtoCell(rng)
For Each cel In rng
CIELABtoXYZ cel.Offset(, -3), cel.Offset(, -2), cel.Offset(, -1)
XYZtoRGB X, Y, Z
cel.Interior.Color = RGB(R, G, b)
Next
End Sub
the code here is specific to changing the colour of column E, from values in B, C and D
Code:
Sub CIELABtoRGB()
LabtoCell range("E11:E15")
'--
'1º) Convertimos CIELAB A XYZ
'CIELABtoXYZ [B3], [C3], [D3]
'--
'2º) Convertimos XYZ a RGB
'XYZtoRGB X, Y, Z
'[F3] = R: [G3] = G: [H3] = b
'--
'3º) Coloreamos
'[J2].Interior.Color = RGB(R, G, b)
End Sub
if you add the top procedure and make the above changes to your existing code, the existing button at E2 will now work to change the colour of all the cells E11 to E15
i initially tried to make the code generic, so that you could adjust the position of the coloured cells, but it should have been more basic
you should look at doing a course to learn the basics of implementing macros (vba code procedures), the existing code in your workbook makes it appear that you are more advanced, or at least would know how to run the macros
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete