Use VBA instead of Conditional Formatting for cell color

[Sleeplol]

Hello All,

How could I replace Conditional Formatting with a VBA solution?
If any value within Worksheets("Remove_Reassign").Range("G42:O60") matches a value within table header "Merged" in table ThisWorkbook.Connections("Query - GroupAgnT1") (Table on sheet GroupAssignTable) then that cell is filled with vbGreen???

Conditional formatting looks great and is fast but doesn't work with the SelectionChange events that need to run when cells within the above range are clicked. If the cell ColorIndex is vbGreen (4) then Macro1; else if ColorIndex is 0 then Macro2.

Thanks for any help with this

 

[shknbk2]

Instead of checking the ColorIndex in the SelectionChange event, can you check to see if the cell matches the same formula/criteria as the conditional format that made it green? It seems like if you make a macro to test the cell to see if it qualifies to be green, make it green, and then test for it being green later, you could test the cell qualification directly.

 

[Sleeplol]

Hi shknbk2,
That's a great idea. I'll make a dummy range for G42:O60, like G73:O91 with this formula IF(ISERROR(VLOOKUP(G42,GroupAssignTable!$AE$6:$AE$4877,1,FALSE)),FALSE,TRUE)
Before I had CF change the color to green if True. But per your suggestion, I'll run a macro to change all True in the range to vbGreen