Forum Discussion
Daniel Cook
Aug 24, 2017Copper Contributor
How do I make excel change the colour of a cell depending on a different cells date?
Hi, How do I make excel change the colour of a cell depending on a different cells date? Turn A2 red if - E2 cell is smaller than todays date Turn A2 yellow with red outline if - E2 ce...
- Nov 21, 2022
Let's say you want to highlight a row if column F contains "Closed".
Use the formula
=$F2="Closed"
If you want something different, please provide more detailed information.
HansVogelaar
Jan 24, 2023MVP
Select A8:A100 (or as far down as you want).
A8 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=D8="Closed"
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Repeat these steps, but with the formula
=D8="Open"
and another color.
JestonH
Apr 19, 2023Copper Contributor
HansVogelaar I tried this approach, but the entire column of cells is still based on D8 instead of the cell in the corresponding row in A. (A10 should be based on D10, A21 on D21, etc...)
- SergeiBaklanNov 04, 2023MVP
wjwhittle , you are welcome
- wjwhittleNov 03, 2023Copper Contributor
SergeiBaklan Thank you! works perfectly
- SergeiBaklanNov 03, 2023MVP
For the blue you may use conditional formatting rule with formula
=COUNTIF($H:$H,B1)
applied to $B$1:$F$50.
Another similar rule is for red.
- wjwhittleNov 03, 2023Copper ContributorHi Sergei,
This thread seems the closest I can find, I have a list of students in Column H and a List in Column I
I need to swap in and out between H and I.
When in H I want cells B1: F50 if they match what is in H to be blue and if in I to be red
What conditional formatting formula do I need so I don't need to individually match and change the colour if I move into the other column?
TIA - MrKasifsOct 11, 2023Copper Contributor
When I insert a column - it seems to inherit the formatting - so its AutoMagic !
I keep the most current stats on the left of the sheet next to the legend - the Old data moves out to the right ... - SergeiBaklanOct 10, 2023MVP
That means you may apply the rule to the range $B$2:$XFD$1000000 if you don't care about performance, but it's better to take more realistic gap.
- MrKasifsOct 10, 2023Copper ContributorYes - the range is continually expanding each day - more data ...
- SergeiBaklanOct 09, 2023MVP
In previous post
"Tomorrow I will insert the column for the October 9th"
That means you are expanding the range, isn't it? Or you always have exactly the same number of columns?
- MrKasifsOct 09, 2023Copper ContributorBut you see - I do not want to expand the range - the sheet above depicts the data exactly as I wanted
The range will always Only be the column to the right of the column to be compared. - SergeiBaklanOct 09, 2023MVP
You may expand you range to the right as necessary (here is to AZ column) and additionally check if next cell is not empty
Rule iterates all cells within the range. Since we use relative references, B2 is compared with C2, C2 with D2, D2 with E2, etc. to the right and down.
You may check in attached file.
- MrKasifsOct 08, 2023Copper ContributorBut you see I am comparing
K <> L
J <> K
I <> J
etc...
B <> C
Tomorrow I will insert the column for the October 9th - SergeiBaklanOct 08, 2023MVP
In first case since the range you applied the rule starts from C2, condition also shall start from it, i.e.
Cell value > C2
Cell value < C2
- HansVogelaarOct 08, 2023MVP
You set up the rule incorrectly - as you can see in your first screenshot, the rules apply to C2:K19.
Since they refer to A2, the rules should have applied to B2:K19 (or even further to the right)...
- MrKasifsOct 08, 2023Copper Contributor
No, the requirements have not changed - you will observe that the values in the yellow boxed sections are incorrectly highlighted - because
D2 is < E2 = Green not Red
J2 < K2
G6 < H6So I need to set up the conditional formatting for Every Column relative to the previous column.
BUT At least I can use the Format painter to apply it.Thank you,
- Scottsgt739Oct 05, 2023Copper Contributor
I had a feeling it was not a simple answer. I greatly appreciate the help. I will try out the macro in the Visual Basic Editor.
- HansVogelaarOct 04, 2023MVP
Excel does not have a built-in way to do this. This will require VBA code.
Copy the following macro into a module in the Visual Basic Editor:
Sub ColorCells() Const shn1 = "Sheet1" ' Name of first sheet Const shn2 = "Sheet2" ' Name of second sheet Const col1 = "A" ' Column on first sheet Const col2 = "A" ' Column on second sheet Dim wsh1 As Worksheet ' First sheet Dim wsh2 As Worksheet ' Second sheet Dim rng1 As Range ' Cell on first sheet Dim rng2 As Range ' Cell on second sheet Application.ScreenUpdating = False Set wsh1 = Worksheets(shn1) Set wsh2 = Worksheets(shn2) wsh2.Columns(col2).Interior.ColorIndex = xlColorIndexNone For Each rng2 In Intersect(wsh2.Columns(col2), wsh2.UsedRange) Set rng1 = wsh1.Columns(col1).Find(What:=rng2.Value, LookAt:=xlWhole) If Not rng1 Is Nothing Then If rng1.Interior.ColorIndex <> xlColorIndexNone Then rng2.Interior.Color = rng1.Interior.Color End If End If Next rng2 Application.ScreenUpdating = True End Sub
Change the constants at the beginning of the macro as needed.
Run the macro whenever you want to update the colors on the second sheet.
- Scottsgt739Oct 04, 2023Copper ContributorThey have been colored manually.
- HansVogelaarOct 04, 2023MVP
Have the names on Sheet1 been colored manually or by a conditional formatting rule?
- Scottsgt739Oct 03, 2023Copper ContributorHans (exemplary knowledge of Excel) what about this scenario. I have names in a column in Sheet1. Some of the names are colored purple. These same names are in Sheet2 in a column. I want the same names that are colored purple in Sheet1 to be colored purple in Sheet2.
- HansVogelaarOct 02, 2023MVP
If your requirements have changed, please explain them again in detail, preferably with an example.
- MrKasifsOct 02, 2023Copper ContributorSorry They match both conditions - related to column A and Each other B-C / C - D / D- E. My bad - i apologize for my confusion.
- HansVogelaarOct 01, 2023MVP
Yes - once again, that is exactly what the rule that I proposed does.
The formula =A2 applies to cell B2. In this formula, both the column reference A and the row reference 2 are relative. This means that Excel will automatically adjust them for other rows and columns.
So in B3, Excel will use =A3, in B4, it will use =A4 etc.
And in C3, Excel will use =B3, in D3, it will use =C3 etc.
If I had used =$A$2, the column and row references would have been absolute, i.e. fixed.
- MrKasifsOct 01, 2023Copper ContributorHello Hans,
Um... but it looks as if it is comparing the contents of the Cells B C D E to the value in Cell A ?
I would like to compare the Value for Sep 26 with the value for Sep 25 ( is the 26th < Green > Red
compare the Value for Sep 27 with the value for Sep 26 ( is the 27th < Green > Red
compare the Value for Sep 28 with the value for Sep 27 ( is the 28th < Green > Red
compare the Value for Sep 29 with the value for Sep 28 ( is the 29th < Green > Red
Better explanation - or are we still Apples and Oranges 🙂
Thank you for your time,
Don - HansVogelaarSep 27, 2023MVP
Yes, that's how I interpreted your question. The conditional formatting rule that I proposed should do that.
- MrKasifsSep 27, 2023Copper ContributorHello,
Very close - but It's my fault for not being clear in my request / explanation.
Every column is a different day - and I want to indicate from day to day did things get *Better* or Worse 🙂
Was Saturday better or worse than Friday, Friday than Thursday
The comparison is between two columns. A:B / B:C / C:D / D:E etc...
Thank you for taking the time.
2023-09-27 2023-09-26 2023-09-25
2630 2621 2620
5874 5867 5868
2126 2122 2124
4357 4354 4355
918 920 920
8850 8825 8818
7062 7055 7062