Conditional Formatting More Than three

[Bob]

What is the code to change the font to bold and also the color In a range in
Column D for five or six different conditions? For example:

West change to RED
East Change to BLUE
North Change to Green
Central Change to Yellow

Thanks in advance.

 

[Don Guillett]

Merry Xmas from Texas
You could use a worksheet_change event in the sheet module with select case

 

[Bob]

Can you give me the complete code that should be added?

 

[Don Guillett]

In case you can't figure this out. Right click sheet tab>view
code>copy/paste this>change color numbers to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case LCase(Target)
Case "west": mc = 4
Case "east": mc = 5
Case "north": mc = 6
Case "central": mc = 7
Case Else
End Select

With Target
.Interior.ColorIndex = mc
.Font.Bold = True
End With
End Sub

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim iColor As Long
Set R = Range("D1100") 'adjust to suit
If Intersect(Target, R) Is Nothing Or _
Target.Count > 1 Then Exit Sub
Vals = Array("WEST", "EAST", "NORTH", "SOUTH")
Nums = Array(3, 5, 10, 6)
For i = LBound(Vals) To UBound(Vals)
With Target
If UCase(.Value) = Vals(i) Then iColor = Nums(i)
If UCase(.Value) = Vals(i) Then .Font.Bold = True
End With
Next
With Target
.Interior.ColorIndex = iColor
End With
End Sub

Pasted into the sheet module.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Just a caveat with Don's code.

Cell font will turn Bold even if cell does not meet case select criteria.

Don't know if that matters or not.


Gord

 

[Bob]

The code works great if I retype the name in the cell. The sheet is already
filled out, is there a way to auto update with out retyping every name in the
column over?

 

[Don Guillett]

Good point

With Target
.Interior.ColorIndex = mc
if target.interior.colorindex>0 then .Font.Bold = True
End With

 

[Gord Dibben]

Option Compare Text
Sub colorit()
For Each cell In ActiveSheet.UsedRange
With cell
Select Case .Value
Case Is = "West"
..Font.Bold = True
..Interior.ColorIndex = 3
Case Is = "East"
..Font.Bold = True
..Interior.ColorIndex = 5
Case Is = "North"
..Font.Bold = True
..Interior.ColorIndex = 4
Case Is = "Central"
..Font.Bold = True
..Interior.ColorIndex = 6
Case Else
..Font.Bold = False
..Interior.ColorIndex = 0
End Select
End With
Next
End Sub


Gord