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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Bob" <(E-Mail Removed)> wrote in message
news:F188213B-B812-45C3-AB68-(E-Mail Removed)...
> 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.

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
> (E-Mail Removed)
> "Bob" <(E-Mail Removed)> wrote in message
> news:F188213B-B812-45C3-AB68-(E-Mail Removed)...
> > 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.
>
>

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
> (E-Mail Removed)
> "Bob" <(E-Mail Removed)> wrote in message
> news:F188213B-B812-45C3-AB68-(E-Mail Removed)...
>> 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.
>

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

On Thu, 25 Dec 2008 08:29:00 -0800, Bob <(E-Mail Removed)>
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.

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"
<(E-Mail Removed)> wrote:

>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

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

Good point

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> 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"
> <(E-Mail Removed)> wrote:
>
>>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
>

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 <(E-Mail Removed)>
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
>>
>>