Change cell fill color for all cells in a workbook based on a cell value

I want to have a work sheet named "Treatment" have a color key located from G3:G22. I want to be able to change the color for example on G3 to yellow. If the value in G3 is "sun" and I select yellow for the fill color, then that is the master/key for the entire workbook. I want to run VBA to look for any cell in the entire workbook and if it has a value of "sun", then it will automatically fill in yellow for the color.

I have the below code that works well for ONE sheet called "Treatment" and the key is from G3:G22, but it only changes the values in column B. Is there any way to run a similar macro for the entire workbook, not just one sheet?

Please Login or Register  to view this content.

Thanks,

This will color each cell in all the sheets in the workbook based on the activecell. Hope you can use/modify this for your purpose.

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

This will color each cell in all the sheets in the workbook based on the activecell. Hope you can use/modify this for your purpose.

Please Login or Register  to view this content.

Thank you for the code John. I tried it out and I am getting a "runtime error '13' type mismatch" and I think it is because I am using general text in my key (G23:G22) and I have cells with the same text is generated from a vlookup and/or index(match) formula.