How to use conditional formatting to highlight a cell with today's date and corresponding cells

mattstan2012

New Member
Joined
May 23, 2024
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi - I have a rota spreadsheete luike this. I want to highlight today's date (i.e 14 Feb - the cell actually contains the full date but is formatted to just show "dd") and the 4 cells BELOW it in the same column. Any ideas? I've had loads of suggestions from ChatGPT, but none have worked and mainly end up highlighting the cells to the LEFT in the same ROW, not the ones BELOW in the same COLUMN

1739534437767.png
 
Basically, you need to use conditional formatting based on formula.
Assuming that you have at least four (three) rows above January (I think you need 5 rows as date 00 and day of week seem to be in 2 rows. If not, adopt the solution as needed)
The formula for conditional formatting (selected cells range for formatting starts A5)
Excel Formula:
=COUNTIF(A1:A5,TODAY())>0
This is screenshot while entering the rule (don't focus on Polish names of weekdays :-))

1739536299331.png


And the result

Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
2
3
4
5Jan0102030405060708091011121314151617181920212223242526
6śrczwptsobniedzponwtśrczwptsobniedzponwtśrczwptsobniedzponwtśrczwptsobniedz
7Empl1ABAB
8Empl2BABA
9Empl3BABA
10
11
12Feb0102030405060708091011121314151617181920212223242526
13sobniedzponwtśrczwptsobniedzponwtśrczwptsobniedzponwtśrczwptsobniedzponwtśr
14Empl1ABAB
15Empl2BABA
16Empl3BABA
17
18
Sheet2
Cell Formulas
RangeFormula
B6:AA6,B13:AA13B6=TEXT(B5,"ddd")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A5:AF16Expression=COUNTIF(A1:A5;TODAY())>0textNO
 
Upvote 0
Important part: The formula you are writing in the rule has to reflect the upper-left corner of the currently selected cells.
The second commant: I've taken into account your "the cell actually contains the full date but is formatted to just show "dd""

But seeing just a screenshot I cannot debug it. It would be much better if you use XL2BB XL2BB - Excel Range to BBCode to show the worksheet contents.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top