A tough formatting quiz !

[gaftalik]

Hello everyone,

In the following table i need to set conditional formatting in C9:
if cell value = D5 (color in red)
if cell value = E5 ( color in blue)
if cell value = F5 (color in yellow)
if cell value = G5 (color in orange)
if cell value = H5 (color in teal)

which means more than 3 criteria.

I tried all the answers given in this thread and others and couldnt solve , is it possible ???

Thank you !

multiple criteria conditional formatting.xls
	B	C	D	E	F	G	H	I
1
2
3		RANK	1	2	3	4	5
4			7	5	4	3	1
5		TYPE	E	B	C	A	D
6
7
8		TYPE
9		A	3
10		B	5
11		C	4
12		D	1
13		E	7
14
15
Sheet1

 

[al_b_cnu]

Thanks this worked too, what if i have under c13 many filled cells for which i have to format all of them to this condition

HI,

You would have to change the line
If Target.Address(False, False) <> "C9" Then Exit Sub

to not exit as appropriate.

What cells do you want this event to operate on?

Alan

 

[gaftalik]

I want to apply on range C9:C100 and other similar ranges such as F9:F100 , H9:H50 , the problem is that i have no ideas about vba

 

[al_b_cnu]

Hi,

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range("C9:C100,F9:F100,H9:H50")
If Union(Target, R).Address <> R.Address Then Exit Sub

Select Case Target.Value
Case Is = [D5]
    Target.Interior.ColorIndex = 3
Case Is = [E5]
    Target.Interior.ColorIndex = 32
Case Is = [F5]
    Target.Interior.ColorIndex = 27
Case Is = [G5]
    Target.Interior.ColorIndex = 46
Case Is = [H5]
    Target.Interior.ColorIndex = 28
Case Else
    Target.Interior.ColorIndex = xlNone
End Select

End Sub

HTH

Alan

 

[gaftalik]

First i want to thank you very much for your concern, i had an error saying compile error Ambiguous name detected:worksheet_change when i applied that code

 

[al_b_cnu]

Hi,

Have you pasted Two worksheet_change events into the module?

Alan

 

[al_b_cnu]

Hi,

Make sure you've not pasted TWO worksheet_change events into the module

Alan

 

[al_b_cnu]

... like I've posted two replies to your question !

 

[gaftalik]

yeah indeed i did that and i deleted it , it worked, can i turn it to macro ?

 

[al_b_cnu]

Hi,

Maybe you can use BrianB's solution, but change the line
Set MYRange = ....
to
Set MyRange = Range("C9:C100,F9:F100,H9:H50")

HTH

Alan

 

[al_b_cnu]

Hi,

Maybe you can use BrianB's solution, but change the line
Set MYRange = ....
to
Set MyRange = Range("C9:C100,F9:F100,H9:H50")

HTH

Alan