Conditional Formatting

[Jar888]

Bit of an odd question I think, but is there a way to conditionally format rows and specific cells in rows based off of a date? Everything is lagged by 24 hours, so the date would always be =TODAY()-1.

First day and last day rows of current month we are inputting data into will be red as shown in example, with input cells also being red. But I want the specific days cells to be highlighted green to indicate data to be put into those cells specifically. The people inputting the data aren't technically savvy, so making it easier for them to navigate is huge for us.

Let me know if this is possible. At the moment we highlight all the cells as mentioned above and just block fill them red.

Any advise or help is greatly appreciated.

 

[Jamie McMillan]

Hello,

Go to conditionally formatting on the home tab, select 'Choose formula to determine which cells to format'

Enter this formula

=$A136=TODAY()-1

The select 'format painter to paste it to all the other cells.

NB - You may want to use this formula =$A136=TODAY() if it is a day off.

Jamie

 

[Jar888]

Not quite what I'm looking for. I want to highlight all the cells in the months data that require input(We track monthly data mostly), with only yesterday's date (10/2/22 in today's case) being highlighted green. What you said will work for the last bit, but having the first and last day of the month's entire row highlighted and the rest of the months inputs are also required.

 

[Jamie McMillan]

Hello,

Go to conditionally formatting on the home tab, select 'Choose formula to determine which cells to format'

Add These formulae to the Cell

=$A136=EOMONTH($A136,0)
=$A136=EOMONTH($A136,-1)+1

The select 'format painter to paste it to all the other cells.

They will highlight the first and last rows.

I need to take my daughter to sleep now. I will have a look later.

Jamie

 

[Jamie McMillan]

Hello,

If you go to conditional formatting as before. (I used row 136 before - I have changed it now. I have put the conditional formatting in C126 (you can change that obviously).

=$A126=EOMONTH($A126,0)
=$A126=EOMONTH($A126,-1)+1
=C126=""

Go to the conditional formatting menu and select 'Manage Rules' Make sure they are in the same order as above. (You can move them on the top Menu bar. Select - 'Stop if True' for the first two. Then paste painter to the other cells.

Notes:- You can select a different shade for the start and end days, also you can put in =$A126=TODAY()-1 and highlight the row for today also.

Jamie

 

[Jar888]

I originally did what you said, but found that it highlights every first and last day of every month rather than just the current.

I changed it to
=$A126=EOMONTH(TODAY()-1,0)
=$A126=EOMONTH(TODAY()-1,-1)+1

This solved the issue and highlights the current months first and last days entire row.