Searching with Macros

[Kura of Tyren]

Does anyone know how I can make a macro that will search
all cells within a preset range and change the font color
of all cells with a value of less than 20 to red while
leaving all other cells in the region with their normal
color?

 

[Frank Kabel]

Hi
why not use Conditional Format for this?. Simply select your data range
and goto 'Format - Conditional Format' for this

 

[Don Guillett]

something like this

for each c in selection 'or range("a2:x234")
if c<20 then c.interior.colorindex=34 'change number to suit
next

 

[Guest]

-----Original Message-----
Does anyone know how I can make a macro that will search
all cells within a preset range and change the font color
of all cells with a value of less than 20 to red while
leaving all other cells in the region with their normal
color?
.

This should work!

Sub FillCell()

'sets start area to check, in this example 1,1 or A1
startrow = 1
startcolumn = 1

'sets end of area to check, in this example 3,14 or c14
endcolumn = 3
endrow = 14

For x = startrow To endrow
For y = startcolumn To endcolumn
If Cells(x, y).Value < 20 Then
'sets colour to red
Cells(x, y).Interior.ColorIndex = 3
Else
'clears format
Cells(x, y).Interior.ColorIndex = xlNone
End If
Next y
Next x

End Sub

 

[Don Guillett]

TESTED
Sub colorem()
Range("a2:x234").Cells.Interior.ColorIndex = 0
For Each c In Range("a2:x234")
If c <> "" And c < 20 Then c.Interior.ColorIndex = 3
Next
End Sub