Cell formatting based on value in cell below

[matt9031]

Hi, I have a large dataset and am trying to find a quick way to format certain cells without setting up hundreds of conditional formatting rules.

Where a cell = "A", I would like the cell above to highlight in Yellow. Where a cell = "C", I would like the cell above to highlight in Green.

Please can someone advise the best way to accomplish this, a macro or conditional formatting. Not had much luck so far.

Appreciate your guidance. Thanks.

 

[Fluff]

Hi & welcome to MrExcel.
Select the range you want this to work on & use

=A2="A"

change the A2 to reflect the 1st column & 2nd row of the selected range

 

[Toadstool]

Hi Matt9031,

Like this?

Matt9031.xlsx
	A	B	C	D	E	F	G
1	Item 1			1500	100	330	220
2				A	A	C	C
3	Item 2	22	44
4		A	A
5	Item 3	404		450	450		55
6		A		C	C		A
7	Item 4	404			221		55
8		A			A		C
9	Item 5	323			450		455
10		C			C		C
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:G10	Expression	=B2="C"	text	NO
B1:G10	Expression	=B2="a"	text	NO

 

[matt9031]

Thank you so much.

 

[Fluff]

Glad we could help & thanks for the feedback.