Oct 05 2018 01:52 AM
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.
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!
Oct 05 2018 01:59 AM
Oct 05 2018 01:59 AM
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
Oct 05 2018 02:21 AM
Oct 05 2018 02:21 AM