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]

Hi,

how about a worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) <> "A5" 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
End Select

End Sub

right click the sheet tab, select 'view Code' & paste in the above.

HTH

Alan

 

[gaftalik]

whats after that please?

 

[BrianB]

So you need to run a macro. Try this :-

Sub SetColour()
    Dim MyRange As Range
    Dim Mycolour As Integer
    '--------------------------
    Set MyRange = ActiveSheet.Range("C9:C13")
    For Each c In MyRange.Cells
        If c.Value = ActiveSheet.Range("D5").Value Then
            Mycolour = 46
        ElseIf c.Value = ActiveSheet.Range("E5").Value Then
            Mycolour = 41
        ElseIf c.Value = ActiveSheet.Range("F5").Value Then
            Mycolour = 6
        ElseIf c.Value = ActiveSheet.Range("G5").Value Then
            Mycolour = 44
        ElseIf c.Value = ActiveSheet.Range("H5").Value Then
            Mycolour = 14
        Else
            Exit Sub
        End If
        c.Interior.ColorIndex = Mycolour
    Next
End Sub

 

[BrianB]

Ooops!! Leave out the Exit sub

 

[gaftalik]

Thanks both of you , that worked, but do you think that in order to return the cells unfilled i have to do it manually ?

 

[al_b_cnu]

Hi,

Just noticed that you require this for cell C9, so:

Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Address(False, False) <> "C9" 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]

i copied and paste that but it didnt appear in macro what am i supposed to do after if not running a macro ?

 

[al_b_cnu]

i copied and paste that but it didnt appear in macro what am i supposed to do after if not running a macro ?

Did you right-click the sheet tab, select 'view Code' & paste the code into that window?

all you have to do then is change cell C9 for it to colour format the cell.

HTH

Alan

 

[gaftalik]

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