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

 

[etaf]

you can setup a new rule

using an AND(

so this will test if the date is less than 4 days from today - and so only true if thats correct
C10 < today()+4

OR(
AND (B10 > = 500 , B10 < = 1000)
AND (B10 > = 4500 , B10 < = 6000))

so add those together

=AND(C10<TODAY()+4,OR(AND(B10>500,B10<1000),AND(B10>4500,B10<6000)))
should be the formula you need

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

Highlight applicable range >>
B10 - 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(C10<TODAY()+4,OR(AND(B10>500,B10<1000),AND(B10>4500,B10<6000)))

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


A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Ian1976]

Thanks, just trying to upload an example... Excel(Not responding)

 

[etaf]

ok, for some reason, my formula has changed

=AND(C10>TODAY()+4,OR(AND(B10>=500,B10<=1000),AND(B10>=4500,B10<=6000)))

so C10 has to be later than today() + 4 days
and i have included = in the number ranges

not sure why that changed ... i must have edited

I have added the xl2bb here
so you can copy into excel and test

Book4
	A	B	C
1
2
3
4
5
6
7
8
9
10		1000	4/25/23
Sheet1

Cell Formulas
Range	Formula
C10	C10	=TODAY()+12

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

 

[Ian1976]

Hopefully this has worked?

Book1
	A	B	C	D	E						K	L	M	N	O	P	Q
1																13/04/2023
2
3
4
5
6
7
8
10		4658	01-Mar-23	9658
12		1658	30-Mar-23	6658
13		4085	01-Apr-23	4085
14		1160	27-Mar-23	1160
16		148	29-Mar-23	148
17		3252	02-Apr-23	3252
18		4109	09-Apr-23	9109
19		2966	29-Mar-23	7966
21		2827	01-Apr-23	7827
22
23
24
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
30
31
32
33
35
Sheet1

Cell Formulas
Range	Formula
P1	P1	=Sheet2!P1
B10,B12:B14,B16:B19,B21	B10	=IF(D10>5000,D10-5000,D10)

 

[etaf]

ok , thats helpful

we dont have P1 - whats that date ??? is it today

and the cell value

so between 1000 and is that minus -500
you original post said number between 0 and 10000

I have a number between 0 and 10000 in cell B10

within 4 days - today is the 13th April
so anything upto and including the 17th April - should not colour - only dates greater than 4 days - so greater than the 17th April

BUT what about earlier dates - so is that also within 4 days
so 9th April to 17th April ... inclusive - does not colour all other dates do colour if the numbers are within the rules

in which case , all your examples would highlight just because of the date - NOT the number, ignoring that - at moment , just need to understand the dates ???
except row 18 which is the 9th April

 

[Ian1976]

ok , thats helpful

we dont have P1 - whats that date ??? is it today

and the cell value

so between 1000 and is that minus -500
you original post said number between 0 and 10000


within 4 days - today is the 13th April
so anything upto and including the 17th April - should not colour - only dates greater than 4 days - so greater than the 17th April

BUT what about earlier dates - so is that also within 4 days
so 9th April to 17th April ... inclusive - does not colour all other dates do colour if the numbers are within the rules

in which case , all your examples would highlight just because of the date - NOT the number, ignoring that - at moment , just need to understand the dates ???
except row 18 which is the 9th April

Hi,

Sorry I'm terrible at explaining things! As there are multiple sheets in the workbook the date is entered once manually into P1 and then P1 on several other sheets reference that first one.
Cell value is just 13/04/23 formatted as Date

Regarding the cell value the number in column B will be from -500 to 10000, but needs highlighting if its between 1000 and minus 500 (-500) or between 4500 and 6000

With the dates, if the date is 4 days or more earlier than the current date then the rules for highlighting are required to be active, if its less then do nothing, if that makes sense?

So if cell P1 was showing todays date, and a date in column C was the 09/04/23, 10/04/23, 11/04/23 or 12/04/23 then no formatting would be required if it was older than that then the formatting would be required.

Thanks for looking, appreciate your time!

 

[etaf]

do you need to compare to P1 or will it always be todays date
in which case we can use today() and not worry about P1

also
what happens if the date is tomorrow - so today() is 13 april - if the date is the future - what to do ?

i have used

=AND(B10<>"",C10<=TODAY()-4,OR(AND(B10>=-500,B10<=1000),AND(B10>=4500,B10<=6000)))

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
D10:D23	Expression	=AND(B10<>"",C10<=TODAY()-4,OR(AND(B10>=-500,B10<=1000),AND(B10>=4500,B10<=6000)))	text	NO

 

[Ian1976]

the date should always be the current date so today() should be ok!

 

[etaf]

ok, see the example i updated

maybe you could also add some examples and colour manually - just to be sure its doing what you want - I had to add a couple more examples to the sheet