Forum Discussion
Colormatch function in excel (using VBA code)
I am preparing a scoreboard for different projects. 10 KPIs of different weightage to measure each project performance. Each KPI will have either 3 responses (good, bad, ugly). User will update the response as either good or bad or ugly then i will use conditional formatting to color the cell as Green, orange and Red for good, bad and ugly. Once after all the 10 KPIs response received, based on the colormatch function, i need to formulate the scoreboard. For reference, i am giving exactly those 3 colors in some other cells for the formulae reference. But i am facing errors... even though the response of 1 KPI is in green and it is not matching with the green colored cell (reference cell) because, "good" is mentioned in the cell, where as green reference cell is empty. i cannot update these response to reference cells because each kPI have different responses. Can someone suggest me a solution to derive a scoreboard
8 Replies
Note that Interior.Color returns the color that has been MANUALLY applied. It ignores any Conditional formatting applied colors. To cater for conditional formatting colors, use Range.DisplayFormat.Interior.Color
Why not just use conditional formatting?
Why not just use conditional formatting? It would be much easier to set up.
Colormatch is not a built-in function, please show us the source code.
Function COLORMATCH(cell1 As Range, cell2 As Range) As Boolean
Dim color1 As Long
Dim color2 As Long
color1 = cell1.Interior.color
color2 = cell2.Interior.color
COLORMATCH = (color1 = color2)
End Function
I suspect the colors you used are not the same. you could use the same drop downs and same conditional formatting rule(s) in row 1 to "produce" those colors
Better yet, get rid of the VBA/macro and just check the VALUE in the cell. IF(F7="On time", D7, ....
Please provide us with some sample data, the current formula and the expected outcome(s). Also, for me, your current description of the problem is confusing. Can you please try to rephrase it?
Hi, If you see in the image attached in my question, when user updates as "On time" from dropdown options in the 1st KPI row.. It will automatically displayed with Green color because of conditional formatting. To use the Colormatch function, i just manually filled the same color in F1 cell for reference. If i use the following formulae "If(Colormatch(F1:F4),True,False)" my expected outcome should be "True" but it is coming as "False".
