macro: changing color of coloumn header based on rows color

Hi Experts,

I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine

Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.

Thanks & Regards,
Raj

Somthing like this...

Please Login or Register  to view this content.

i.e. it counts the number of cells in the range with either red or yellow text in the variables r and y respectively. If either is > 0 then sets the colour of the header accordingly.

You could do it all using conditional formatting of course. For the second bit you might want an array formula.
={IF(A2:A5>0.2,IF(A2:A5<0.5,"Title ","Title "),"Title")}
And format based on "Title ", "Title " or "Title" I.e. the spaces being critical.

Hope that helps...

Hi,

Thanks for the solutions. I tried to implement based on the solutions received, but i am facing problem when ever the cell value is Green.

To brief, the rows cells are colored based on conditional formatting . Values are Red and Green.

Based on the color of the cell, corresponding header row should have that color.

Samle Coding :

Please Login or Register  to view this content.

First, based on conditional formatting i am getting colors of the Rows cell, then reading each cell to find out is the color is red or green. It is working fine for Red, but some how there is a problem in reading yellow color cell even though that cell color is present.

I pasted the code that i am using. Could you please look and help me if i am doing any mistake.

KR,
Raj

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here