Conditional Formatting More Than three
 

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.

Conditional Formatting More Than three
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob" wrote in message
...
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.

Conditional Formatting More Than three
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
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
End If
End Sub


--
mikeaj72
------------------------------------------------------------------------
mikeaj72's Profile: http://www.thecodecage.com/forumz/member.php?userid=46
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44176

Conditional Formatting More Than three
 

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

"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob" wrote in message
...
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.

Conditional Formatting More Than three
 

In case you can't figure this out. Right click sheet tabview
codecopy/paste thischange 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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob" wrote in message
...
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.

Conditional Formatting More Than three
 

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

On Thu, 25 Dec 2008 08:29:00 -0800, Bob
wrote:

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.

Conditional Formatting More Than three
 

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

On Thu, 25 Dec 2008 11:10:04 -0600, "Don Guillett"
wrote:

In case you can't figure this out. Right click sheet tabview
codecopy/paste thischange 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

Conditional Formatting More Than three
 

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?

"mikeaj72" wrote:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
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
End If
End Sub


--
mikeaj72
------------------------------------------------------------------------
mikeaj72's Profile: http://www.thecodecage.com/forumz/member.php?userid=46
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44176

Conditional Formatting More Than three
 

Good point

With Target
.Interior.ColorIndex = mc
if target.interior.colorindex0 then .Font.Bold = True
End With


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 25 Dec 2008 11:10:04 -0600, "Don Guillett"
wrote:

In case you can't figure this out. Right click sheet tabview
codecopy/paste thischange 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

Conditional Formatting More Than three
 

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

On Thu, 25 Dec 2008 09:40:01 -0800, Bob
wrote:

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?

"mikeaj72" wrote:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
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
End If
End Sub


--
mikeaj72
------------------------------------------------------------------------
mikeaj72's Profile: http://www.thecodecage.com/forumz/member.php?userid=46
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44176