Conditional Formatting to highlight the previous cell if the statement is true

[ExcelNewbie2020]

Sirs/Ma'ams,

My formula is working good with conditional formatting if the statement is true. It highlight the cell as normal.

Now, i would like to highlight the previous cells (instead of current) if the statement is true. See below

This is the formula that I use..
=IF(COUNTIFS(Sheet1!$B$9:$B$500,$B6,Sheet1!$K$9:$K$500,G$5,Sheet1!$L$9:$L$500,"Active"),TRUE,"")

I hope I explain it well..

Many Thanks,

 

[Fluff]

Is that formula in the cell or in conditional formatting?

 

[ExcelNewbie2020]

Is that formula in the cell or in conditional formatting?

in conditional formatting.. the cell has no formula..

 

[Fluff]

In that case you can use it to highlight whichever column you want. Just apply it to any columns that should be coloured.
Also you don't need the IF you can just use

=COUNTIFS(Sheet1!$B$9:$B$500,$B6,Sheet1!$K$9:$K$500,G$5,Sheet1!$L$9:$L$500,"Active")

 

[ExcelNewbie2020]

In that case you can use it to highlight whichever column you want. Just apply it to any columns that should be coloured.
Also you don't need the IF you can just use

=COUNTIFS(Sheet1!$B$9:$B$500,$B6,Sheet1!$K$9:$K$500,G$5,Sheet1!$L$9:$L$500,"Active")

thank you.. I have a wide range of column.. from W1 to W100.. I did highlight it all, but its highlighting the current cell not the previous cell..

 

[Fluff]

Excel does not have columns W1 to W100.
If it is not working, then you will need to post some data & not an image that shows nothing of any value.

 

[ExcelNewbie2020]

Excel does not have columns W1 to W100.
If it is not working, then you will need to post some data & not an image that shows nothing of any value.

sorry, W1 to W100 is my column header.. It is working, but my question is how do I select the cells to highlight if I need to highlight the whole range?.. For Example

I selected the above cells (K1:V11) then apply the condi formatting. W6 (P) highlighted when it triggered the formula. but I need to highlight W5 (O) instead.

 

[Fluff]

Without seeing your data I cannot help, so can you please post it.

 

[ExcelNewbie2020]

Without seeing your data I cannot help, so can you please post it.

I hope this will help...

sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	1	NAME1	W2	Active			W1	W2	W3	W4	W5	W6	W7	W8	W9	W10	W11	W12	W13	W14	W15	W16	W17	W18	W19	W20
2	2	NAME2	W2	Active		NAME1
3	3	NAME3	W2	Active		NAME2
4	4	NAME4	W2	Active		NAME3
5	5	NAME5	W2	Active		NAME4
6	6	NAME6	W2	Active		NAME5
7	7	NAME7	W3	Active		NAME6
8	8	NAME8	W3	Active		NAME7
9	9	NAME9	W3	Active		NAME8
10	10	NAME10	W3	Active		NAME9
11	11	NAME11	W3	Active		NAME10
12
13
14			Instead of W2, I need to highlight W1
15			Same in W3, it should be W2
16
17			Conditional Formatting Formula =COUNTIFS($B$1:$B$11,$F2,$C$1:$C$11,G$1,$D$1:$D$11,"Active")
18
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:Z11	Expression	=COUNTIFS($B$1:$B$11,$F2,$C$1:$C$11,G$1,$D$1:$D$11,"Active")	text	NO
D1:D11	Cell Value	="Hold"	text	NO
D1:D11	Cell Value	="In-process"	text	NO
D1:D11	Cell Value	="Done"	text	NO

 

[Fluff]

Ok, thanks for that, change the formula to

=COUNTIFS($B$1:$B$11,$F2,$C$1:$C$11,H$1,$D$1:$D$11,"Active")