How can I copy Conditional Formatting on cells below while the formula changes along?

Copper Contributor

I have made a simple Excel spreadsheet for myself so that I can easily see who I've sent a connection request, who has accepted the request, who I've contacted and who has reacted positively. For each of these aspects I type in 'ja' when it fits the requirement of the aspect, if it doesn't I leave the cell empty.

 

As you can see I have added a column (column G) in which the cells count the times that the cells on the left side contain the word "ja", I only did this in order to make the formula a bit easier to make. The thing is, I want to change the cells below column B into a certain colour based on the number that is counted in column G. This goes as such (number is meant for column G, colour is meant for column B): 1 = red, 2 = orange, 3 = yellow, 4 = green. 

In other words: If the number in G2 is 4, I want the colour of B2 to be green and if the number in G3 is 1 I want the colour of B3 to be red. 

 

Vraag over Excel - foto GOED.JPG

 

Now I'll describe my problem. I understand what Conditional Formatting is and as you can see I already used it on cell B2. I want to attach the same rules, as described earlier, to the cells below B2 (I only captured a very small part of the spreadsheet in the picture, the file consists of many more cells), but I want B2 to be connected to G2, B3 to G3, B4 to G4 and so on. The problem is that if I copy the Conditional Formatting of B2 to the cells below, they all reference to the cell G2, they don't link to the right cell.

 

Can someone tell me how I can copy the Conditional Formatting to the right cells in column B so that they connect/reference to the right cell in column G (cells in the same row)?

 

Thank you in advance!

 

4 Replies
initially I will suggest that you remove the $ sign in reference cell.
I'm sure it is written as $G$2 - make it $G2 --- meaning the column G is absolute while the number (row) will change accordingly.
HTH

Hi Giel,

 

You may create 4 conditional formatting rules, one for each color, with formulas

for red

=$G2=1

and apply it to your range in B, e.g. $B$2:$B:$100

Same for other colors

Thank you! That already did it for me.