Colour Macro

  • Thread starter Thread starter Ntisch
  • Start date Start date
N

Ntisch

Hi ,

I apologise in advance for the rudimentary nature of this post but I am
new to Macros.

What I am trying to do is create a macro that changes the colour of a
word's font after it has been typed into a cell. For example when I
type the word red into a cell I would like to be able to run a macro to
turn the entry into the word red (typed in the red font colour).

An associated question. Is it possible to have more than three types of
conditional formatting within a single spreadsheet?

thanks, Nik
 
Hi
conditional format only accepts 3 conditions though you have a fourth
if you include the default format.
If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6
different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the text in A1 red if the value in A1 is equal
to 'red_word':

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value "red_word" Then
.font.colorindex = 3
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


HTH
Frank
 
Nik,

You need event code like so

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "red": .Font.ColorIndex = 3
Case "green": .Font.ColorIndex = 10
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

which goes in the worksheet code module. Change the range to suit, and add
extra cnditions.

CF has four colurs in practice, as you have the 'no condition' colour. If
you want more than this you need VBA again. This is an example I posted
receently

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Count = 1 Then
If .Column = 1 Then
Select Case .Value
Case Is = 1
.Interior.ColorIndex = 3 'red
Case Is = 2
.Interior.ColorIndex = 38 'pink
Case Is = 3
.Interior.ColorIndex = 4 'green
Case Is = 4
.Interior.ColorIndex = 6 'yellow
Case Is = 5
.Interior.ColorIndex = 8 'majenta
Case Is = 6
.Interior.ColorIndex = 5 'blue
Case Is = 7
.Interior.ColorIndex = 15 'grey
Case Is = 8
.Interior.ColorIndex = 38 'rose
Case Is = 9
.Interior.ColorIndex = 1 'teal
Case Else 'none of the above numbers
Exit Sub
End Select
End If
End If
End With

ws_exit:
Application.EnableEvents = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top