Change Font Colour and Background If Q

[seanryanie]

I wish to change the Font & Background colour if a cell = a certain
value.

I can't use Conditional Formatting as I have 28 variables that the cell
value could be. I have seen an add-in on this Newgroup, but I'm trying
to design a workbook for 5 users and I dont wish to install this on
each Laptop.

My Workbook will have a Data Validation Drop down list that the user
will select a certain location from, if the location Selected =
"London" I wish for the Background to change to Red and Font to White
etc etc

How would I do this through VB

Thanks for any help

 

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<==== 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 LCase(.Value)
Case "london":
.Font.ColorIndex = 2
.Interior.ColorIndex = 3 'red
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

(replace somewhere in email address with gmail if mailing direct)

 

[seanryanie]

Bob

Thank you very much for that. Just one small thing, I'll be able to
work out from here, how would you adjust the code to include another
location, say the location selected was "Birmingham" and I wanted that
Blue

Thanks again

 

[seanryanie]

Bob

Thank you very much for that. Just one small thing, I'll be able to
work out from here, how would you adjust the code to include another
location, say the location selected was "Birmingham" and I wanted that
Blue

Thanks again

 

[seanryanie]

Got it see below



'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "D7:J30" '<==== 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 LCase(.Value)
Case "london":
.Font.ColorIndex = 2
.Interior.ColorIndex = 3 'red

Case "birmingham":
.Font.ColorIndex = 2
.Interior.ColorIndex = 4 'red
End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub

 

[seanryanie]

How would you include code that if the cell is blank the colour of the
cell would revert to "default" i.e. white

Problem at the moment is that if a location is selected the cell is
changed to the required colour but if the cell is changed to "blank"
the original colour remains

Also where can I obtain a list of colour numbers i.e. red=3; green=4
etc


Thanks

 

[Bob Phillips]

well done, that is it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "D7:J30" '<==== 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 LCase(.Value)
Case ""
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlcolroindexnone
Case "london":
.Font.ColorIndex = 2
.Interior.ColorIndex = 3 'red

Case "birmingham":
.Font.ColorIndex = 2
.Interior.ColorIndex = 4 'red
End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Colour list


Public Enum xlColorIndex
xlCIBlack = 1
xlCIWhite = 2
xlCIRed = 3
xlCIBrightGreen = 4
xlCIBlue = 5
xlCIYellow = 6
xlCIPink = 7
xlCITurquoise = 8
xlCIDarkRed = 9
xlCIGreen = 10
xlCIDarkBlue = 11
xlCIDarkYellow = 12
xlCIViolet = 13
xlCITeal = 14
xlCIGray25 = 15
xlCIGray50 = 16
xlCIPeriwinkle = 17
xlCIPlum = 18
xlCIIvory = 19
xlCILightTurquoise = 20
xlCIDarkPurple = 21
xlCICoral = 22
xlCIOceanBlue = 23
xlCIIceBlue = 24
'xlCIDarkBlue = 25
'xlCIPink = 26
'xlCIYellow = 27
'xlCITurquoise = 28
'xlCIViolet = 29
'xlCIDarkRed = 30
'xlCITeal = 31
'xlCIBlue = 32
xlCISkyBlue = 33
xlCILightGreen = 35
xlCILightYellow = 36
xlCIPaleBlue = 37
xlCIRose = 38
xlCILavender = 39
xlCITan = 40
xlCILightBlue = 41
xlCIAqua = 42
xlCILime = 43
xlCIGold = 44
xlCILightOrange = 45
xlCIOrange = 46
xlCIBlueGray = 47
xlCIGray40 = 48
xlCIDarkTeal = 49
xlCISeaGreen = 50
xlCIDarkGreen = 51
xlCIBrown = 53
xlCIIndigo = 55
xlCIGray80 = 56
End Enum



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Gord Dibben]

To get a list of colors and index numbers, copy this to a General Module and run
it.

Sub ListColorIndexes()
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Hex(ThisWorkbook.Colors(Ndx))
Cells(Ndx, 3).Value = Ndx
Next Ndx
End Sub

Note: if you have modified any colors, you will see discrepancies.


Gord Dibben MS Excel MVP

How would you include code that if the cell is blank the colour of the
cell would revert to "default" i.e. white

Problem at the moment is that if a location is selected the cell is
changed to the required colour but if the cell is changed to "blank"
the original colour remains

Also where can I obtain a list of colour numbers i.e. red=3; green=4
etc


Thanks

Gord Dibben MS Excel MVP

 

[seanryanie]

Thanks all for your help

 

[David McRitchie]

Hi Sean,
I am assuming that when you said variables you meant values; otherwise,
I don't understand the question.

I see that you have satisfactory solutions with macros, but
would like to point out that the limitation in Conditional Formatting
in not in how many values but how many conditions that you can
check for per cell. . A condition returns True of False. So
in effect you can have up to 3 generated formats per cell from
Conditional Formatting that can override existing formats.

Each format can change font color, interior color, border width&color.

No limit on the number of values to be used to determine color.
=OR($A1=3, $A1=5, AND($A1>=100, $a1<2000))

More on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm