Excel: change font colour according to cell contain?

hi,



i have a cell with number which will varied ( say from 20 to 1000) once i click on a botton (with macro). I would like the colour of the font to be varied as according to different range of number

example: for number between 20 to 200: font colour=red,

for number beyween 201 to 500: font colour=blue

...etc,

how could i achieve this by using macro? as i use 'format conditioning' function, it only allow me to have 3 types of colour, which i need 5.



please advise.Excel: change font colour according to cell contain?
Assume the cell in concern is A1 of sheet1

Alt-F11 to open VBA editor, and double click on left Sheet1(Sheet1) icon then paste the following code to the right blank area

Private Sub Worksheet_Change( ByVal Target As Range)

If Target.Cells.Count %26gt; 1 Then Exit Sub

If Not Intersect( Target, Range(';A1';)) Is Nothing Then

聽聽聽If Target %26gt;= 20 And Target %26lt;= 200 Then

聽聽聽聽聽聽Range( Target.Address).Font. ColorIndex = 3

聽聽聽ElseIf Target %26gt;= 201 And Target %26lt;= 400 Then

聽聽聽聽聽聽Range( Target.Address).Font. ColorIndex = 44

聽聽聽ElseIf Target %26gt;= 401 And Target %26lt;= 600 Then

聽聽聽聽聽聽Range( Target.Address).Font. ColorIndex = 35

聽聽聽ElseIf Target %26gt;= 601 And Target %26lt;= 800 Then

聽聽聽聽聽聽Range( Target.Address).Font. ColorIndex = 37

聽聽聽ElseIf Target %26gt;= 801 And Target %26lt;= 1000 Then

聽聽聽聽聽聽Range( Target.Address).Font. ColorIndex = 5

聽聽聽Else

聽聽聽聽聽聽Range( Target.Address).Font. ColorIndex = xlNone

聽聽聽End If

End If

End Sub



Hope this helps.Excel: change font colour according to cell contain?
You檙e looking for Conditional Formatting:



At the top of your column, click on



Format, Conditional Formatting ?br>
In the dialog box, enter the range of values and click on the Format?button: choose a color for the range.



Click on Add to apply conditional formatting to a maximum of three different ranges.
Statements like:



If Cells(1, 1) %26gt; 5 Then

Cells(1, 1).Font.ColorIndex = 50

End If



Should do it.



Check the ';colorIndex property'; in VBA help to get the relationship between # and color.