Searching with Macros

K

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?
 
F

Frank Kabel

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

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
 
G

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?
.
Click to expand...

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
 
D

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
 
Top