Over riding conditional formatting?

[Ian1976]

Hi,
Not sure if what I'm trying to achieve is possible?

I have a number between 0 and 10000 in cell B10 that has the following conditional formatting - "Cell Value Between 1000 and -500" & "Cell Value Between 6000 and 4500" which will turn the number in B10 Red

I have a date in cell C10 which if it is within 4 days of todays date (which is also available in cell P1) then I'd like it to ignore the conditional formatting formula?

Is that possible?

Thanks

 

[Ian1976]

OK I've managed to get that working as per your example, and that formatting will be perfect if I can get what you've done on column D to work on Column B, is that possible?

Only needs to work on column B
Thanks

 

[etaf]

yes
just select column B
instead of D in the conditional formatting

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6
7
8								range of numbers
9
10		4658	1-Mar-23	9658		TRUE		TRUE
11						FALSE		FALSE
12		1658	30-Mar-23	6658		FALSE		FALSE
13		4085	1-Apr-23	4085		FALSE		FALSE
14		1160	27-Mar-23	1160		FALSE		FALSE
15						FALSE		FALSE
16		148	29-Mar-23	148		TRUE		TRUE
17		3252	2-Apr-23	3252		FALSE		FALSE
18		4109	9-Apr-23	9109		FALSE		FALSE
19		2966	29-Mar-23	7966		FALSE		FALSE
20						FALSE		FALSE
21		2827	1-Apr-23	7827		FALSE		FALSE
22		-300	13-Apr-23			FALSE
23		-299	8-Apr-23			TRUE
24
25
26		Column D is a Vlookup formaul result from anther sheet within this workbook The cells in column B are required to be red if "Cell Value Between 1000 and -500" & "Cell Value Between 6000 and 4500" But if date in column C is within 4 days of cell P1 then ignore formatting and leave as standard.
27
28
29
30
31
32
33
Sheet2

Cell Formulas
Range	Formula
B10,B21,B16:B19,B12:B14	B10	=IF(D10>5000,D10-5000,D10)
H10:H21	H10	=AND(B10<>"",OR(AND(B10>=-500,B10<=1000),AND(B10>=4500,B10<=6000)))
C22	C22	=TODAY()
C23	C23	=TODAY()-5
F10:F23	F10	=AND(B10<>"",C10<=TODAY()-4,OR(AND(B10>=-500,B10<=1000),AND(B10>=4500,B10<=6000)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B10:B23	Expression	=AND(B10<>"",C10<=TODAY()-4,OR(AND(B10>=-500,B10<=1000),AND(B10>=4500,B10<=6000)))	text	NO

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
B10:B100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(B10<>"",C10<=TODAY()-4,OR(AND(B10>=-500,B10<=1000),AND(B10>=4500,B10<=6000)))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[Ian1976]

Yes that's what I thought, I changed it to =$B$10:$B$27 but it just went blank, lol...

Must be something I've missed typed I'll check over it all! thanks

 

[etaf]

if you change the selection - it will also change the formula

probably easier to copy the formula , and then just delete the formatting in column D

Select the range in column B
and then paste the formula

 

[Ian1976]

Brilliant, that's what I'd done! works great now thanks!

Thanks so much for your help!

 

[etaf]

you are welcome