Macro for multiple conditional formatting

[rsmith]

Hi All

I'm trying to figure out a Macro for multiple conditional formatting which
highlights the cell and changes the font to the same colour as the cell.

For example if the cell has the number "10" in it then I need to have it
shaded in grey and the font grey as well so you can't actually see the number
"10". I'll be doing this for about 5 conditions - all numeric. (including
"0").

Any help would be greatly appreciated

 

[Shane Devenshire]

What version of Excel are you using? If you are trying to make the content
of a cell not visible regardless of the background color, you can set the
cells Custom Format to ;;;

To do this you select the cell(s) and choose Format, Cells, Nunber tab,
Custom and type ;;; in the Type box. (2003) Same basic idea in 2007.

If you still need a macro that does what you stated we need to know all the
details or you can turn on the Macro Recorder and record the manual steps.

 

[D.]

This simple code would give you something to work with

Sub SetColor()

Dim r As Range
Set r = Range("A1")
Dim a, b, c, d, e As Integer
a = 2
b = 5
c = 8
d = 10
e = 25

If r = 1 Then
With r.Interior
.ColorIndex = a
.Pattern = xlSolid
End With
r.Font.ColorIndex = a

ElseIf r = 2 Then
With r.Interior
.ColorIndex = b
.Pattern = xlSolid
End With
r.Font.ColorIndex = b

ElseIf r = 3 Then
With r.Interior
.ColorIndex = c
.Pattern = xlSolid
End With
r.Font.ColorIndex = c

ElseIf r = 4 Then
With r.Interior
.ColorIndex = d
.Pattern = xlSolid
End With
r.Font.ColorIndex = d

ElseIf r = 5 Then
With r.Interior
.ColorIndex = e
.Pattern = xlSolid
End With
r.Font.ColorIndex = e
Else
r.Interior.ColorIndex = xlNone
r.Font.ColorIndex = 0
End If
End Sub