Conditional Formatting for more than 3 conditions

[MMM]

Hi,
I tried downloading from the xldynamic site, but I think I am doing
something wrong. I get a zip file, and when I extract files I do not get a
..ini file only a .xls file and nothing happens after that.

Can someone help me write a Macro for this? It's simple:

I have to look down a range of columns and rows (Say A1: H30) and, based on
the text there, which could be one of the following letters:
1) G
2) R
3) N
4) Y
5) C
6) W

I need a different color for each type of cell: 1) G=Green with dark green
font for the letter, 2) R=Red with red background and black for letter R, 3)
N= Purple background and Yellow font, 4) Y=Yellow background, black font, 5)
C=Blue background dark blue font 6) W= White background, blue font,

I have no clue how to write a Macro, your help will be highly appreciated,
thanks


--
MMM

[Bob Phillips]

It shouldn;t be an xls file but an xla file.

Here is a VBA example that sets the cell colour


Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "B": .Interior.ColorIndex = 5
Case "O": .Interior.ColorIndex = 46
Case "P": .Interior.ColorIndex = 7
Case "R": .Interior.ColorIndex = 3
'etc.
End Select


End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"MMM" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I tried downloading from the xldynamic site, but I think I am doing
> something wrong. I get a zip file, and when I extract files I do not get a
> .ini file only a .xls file and nothing happens after that.
>
> Can someone help me write a Macro for this? It's simple:
>
> I have to look down a range of columns and rows (Say A1: H30) and, based
on
> the text there, which could be one of the following letters:
> 1) G
> 2) R
> 3) N
> 4) Y
> 5) C
> 6) W
>
> I need a different color for each type of cell: 1) G=Green with dark green
> font for the letter, 2) R=Red with red background and black for letter R,
3)
> N= Purple background and Yellow font, 4) Y=Yellow background, black font,
5)
> C=Blue background dark blue font 6) W= White background, blue font,
>
> I have no clue how to write a Macro, your help will be highly appreciated,
> thanks
>
>
> --
> MMM

[Chip Pearson]

Note that Bob's code will run only when the cell's value is
changed by the user, not, as is the case with Conditional
Formatting, when the change is the result of a calculation.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Bob Phillips" <[email protected]> wrote in
message news:[email protected]...
> It shouldn;t be an xls file but an xla file.
>
> Here is a VBA example that sets the cell colour
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then
> With Target
> Select Case UCase(.Value)
> Case "B": .Interior.ColorIndex = 5
> Case "O": .Interior.ColorIndex = 46
> Case "P": .Interior.ColorIndex = 7
> Case "R": .Interior.ColorIndex = 3
> 'etc.
> End Select
>
>
> End With
> End If
>
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a
> standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "MMM" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> I tried downloading from the xldynamic site, but I think I am
>> doing
>> something wrong. I get a zip file, and when I extract files I
>> do not get a
>> .ini file only a .xls file and nothing happens after that.
>>
>> Can someone help me write a Macro for this? It's simple:
>>
>> I have to look down a range of columns and rows (Say A1: H30)
>> and, based
> on
>> the text there, which could be one of the following letters:
>> 1) G
>> 2) R
>> 3) N
>> 4) Y
>> 5) C
>> 6) W
>>
>> I need a different color for each type of cell: 1) G=Green
>> with dark green
>> font for the letter, 2) R=Red with red background and black
>> for letter R,
> 3)
>> N= Purple background and Yellow font, 4) Y=Yellow background,
>> black font,
> 5)
>> C=Blue background dark blue font 6) W= White background, blue
>> font,
>>
>> I have no clue how to write a Macro, your help will be highly
>> appreciated,
>> thanks
>>
>>
>> --
>> MMM
>
>

[MMM]

Bob,

Your add-in is absolutely awesome once I got it to work. Kudos!
--
MMM


"Bob Phillips" wrote:

> It shouldn;t be an xls file but an xla file.
>
> Here is a VBA example that sets the cell colour
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then
> With Target
> Select Case UCase(.Value)
> Case "B": .Interior.ColorIndex = 5
> Case "O": .Interior.ColorIndex = 46
> Case "P": .Interior.ColorIndex = 7
> Case "R": .Interior.ColorIndex = 3
> 'etc.
> End Select
>
>
> End With
> End If
>
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "MMM" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > I tried downloading from the xldynamic site, but I think I am doing
> > something wrong. I get a zip file, and when I extract files I do not get a
> > .ini file only a .xls file and nothing happens after that.
> >
> > Can someone help me write a Macro for this? It's simple:
> >
> > I have to look down a range of columns and rows (Say A1: H30) and, based
> on
> > the text there, which could be one of the following letters:
> > 1) G
> > 2) R
> > 3) N
> > 4) Y
> > 5) C
> > 6) W
> >
> > I need a different color for each type of cell: 1) G=Green with dark green
> > font for the letter, 2) R=Red with red background and black for letter R,
> 3)
> > N= Purple background and Yellow font, 4) Y=Yellow background, black font,
> 5)
> > C=Blue background dark blue font 6) W= White background, blue font,
> >
> > I have no clue how to write a Macro, your help will be highly appreciated,
> > thanks
> >
> >
> > --
> > MMM
>
>
>

[bkcoughlin]

I'm trying to do exactly that--make it change cell color based on the calculated value, and have more than 3 conditions. Is there a modification to this that someone can suggest?

Thanks in advance for any insight anyone can offer.

Brian


[QUOTE=Chip Pearson]Note that Bob's code will run only when the cell's value is
changed by the user, not, as is the case with Conditional
Formatting, when the change is the result of a calculation.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com