Conditional Formatting More Than three

  • Thread starter Thread starter Bob
  • Start date Start date
B

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.
 
Merry Xmas from Texas
You could use a worksheet_change event in the sheet module with select case
 
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
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim iColor As Long
Set R = Range("D1:D100") '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
 
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
 
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?
 
Good point

With Target
.Interior.ColorIndex = mc
if target.interior.colorindex>0 then .Font.Bold = True
End With
 
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
 
Back
Top