Conditional Formatting

Fyldeboy

New Member
Joined
Oct 21, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
I don't usually have a problem with this for my simple needs, but this has got me beat.

1603638284009.png


The value in C48 is a date, 44526 in this case.

I want to apply conditional formatting to the entire range a47:q56 if the displayed value is less than 8

In my head, text and value convert the 44526 in this case to a 1, but I can't get the conditional formatting to change the formatting.

1603638662800.png
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
Get rid of the outer quotes & just use one set of quotes around "dd", you're also missing a closing bracket before the <
 

Fyldeboy

New Member
Joined
Oct 21, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Thanks, That didn't seem to have any effect, so I'm trying a different way, which 'sort of' works.

1603662218372.png

1603662432469.png

To simplify the formatting, cell A46 now has the numerical value of the date in cell u49
If that number is over 6, the whole range above should be greyed out - why just selected cells?
I guessed a49:a56 and c47:w47 because they have pre-existing formatting - but I cleared all formatting from the range to no effect
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
Only cells that have a value greater than 6 will be highlighted, with that formula.
What exactly are you trying to do?
 

Fyldeboy

New Member
Joined
Oct 21, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Only cells that have a value greater than 6 will be highlighted, with that formula.
What exactly are you trying to do?
Trying to get the whole range formatted depending on the value of A46. C49 for example is 100% blank - but formatted how I want it because A46 is higher than 6
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
With the formula you have shown C49 is NOT getting formatted because of the value in A46. Maybe you have other CF rules that are causing that?
If you want to format the entire range just lock the A46 in the formula.
 

Fyldeboy

New Member
Joined
Oct 21, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
I'm grateful for your time trying to help, but still no luck.
I have cleared all formatting (except number format, which isn't affected by CF) from the block of 12 cells below "Mon 07", so what is stopping them having the desired formatting
I've changed the source cell to $A$46 (13) and as that is over 6, everything A47:W56 should be formatted. I can sort of understand that the pale blue or brown formats might over-write the CF, though I'm not convinced that is the case. There is NO OTHER conditional formatting for a47:w56.

1603668243095.png

1603667946080.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
You need to remove the quotes from the formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,353
Messages
5,595,668
Members
414,007
Latest member
bongda360org

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
Top