Conditional formatting again

  • Thread starter @Homeonthecouch
  • Start date
@

@Homeonthecouch

Okay I have had a google and a read and am no wiser as to how I did it or
how to do it

My problem is this put simply.

I have a range of cells called "Name"

When Tom appears in the range I want it to be red
When Jim appears in the range I want it to be blue
When Ron appears in the range I want it to be green
When Sue appears in the range I want it to be pink

I have more names but won't bore you with them all.
Is there a limit to how many I can have?

Once again, Many thanks.

Andrew
 
G

glen.e.mettler

Okay I have had a google and a read and am no wiser as to how I did it or
how to do it

My problem is this put simply.

I have a range of cells called "Name"

When Tom appears in the range I want it to be red
When Jim appears in the range I want it to be blue
When Ron appears in the range I want it to be green
When Sue appears in the range I want it to be pink

I have more names but won't bore you with them all.
Is there a limit to how many I can have?

Once again, Many thanks.

Andrew
Click to expand...

Yes. There are only 4 options - 1 is the default color you select in
the cell. The other 3 can be set with a cell value or a formula.

using your example:
the basic color is pink (Sue)
if the cell value is Tom, it will be red
if the cell value is Jim, it will be blue
if the cell value is Ron it will be green

However, if you use VBA and cycle thru the rows, you can assign any
color you want based on a list of names

Glen
 
B

Bob Phillips

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "Name" '<=-== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "Tom": .Interior.ColorIndex = 3 'red
Case "Sue": .Interior.ColorIndex = 6 'yellow
Case "Jim": .Interior.ColorIndex = 5 'blue
Case "Ron": .Interior.ColorIndex = 10 'green
'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

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top