Forum Discussion
Help with conditional formatting
Below is the screenshot of my spreadsheet:-
I’m currently on the tab Oct23, this is the start data.
Next tabs is Nov23, Dec23 and so on.
What I’m wanting to do is add the new data to Nov23 tab and have the cell colour change based on the number difference to Oct23.
What I’m looking for is:-
Red cell if -2 & below
Light red if -1
No change if the same
Dark green if +1
Light green if +2 & above
The full spread sheet is:-
Coloumns = A to AQ
Rows = 2 to 99
How do I do this?
I know its conditional formatting, but how do I set this up?
Will the names in column A be the same each month, in the same order? If so:
Select C2:AQ99 on the Nov23 sheet.
C2 should be the active cell in the selection.
On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula=Oct23!B2
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.Repeat these steps, but with 'equal to', =Oct23!B2-1 and light red.
Repeat them again, with 'equal to', =Oct23!B2+1 and dark green.
Finally, with 'greater than or equal to', =Oct23!B2+2 and light green.
- Thank you for the easy-to-follow instructions. It's working now.
But would the formulas be different if the list of names changed? It's a game so I'll be buying and selling players.
The spreadsheet is a stat tracker to see if my players are improving or declining.If the names in column A change from month to month, the formulas in the rules become more complicated. In each of the rules, replace Oct23!B2 with
INDEX(Oct23!B:B, MATCH($A2, Oct23!$A:$A. 0))
or with
XLOOKUP($A2, Oct23!$A:$A, Oct23!B:B)
