A tough formatting quiz !

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone, (y)

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
BCDEFGHI
1
2
3RANK12345
475431
5TYPEEBCAD
6
7
8TYPE
9A3
10B5
11C4
12D1
13E7
14
15
Sheet1
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,518
Hi,

how about a worksheet change event:
Code:
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
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
So you need to run a macro. Try this :-

Code:
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127

ADVERTISEMENT

Ooops!! Leave out the Exit sub
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,518

ADVERTISEMENT

Hi,

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

Code:
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

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,518
gaftalik said:
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

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,064
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top