2) Replace

With ws.Cells

with

With ws.Range("A1:AW22")

1) One way (assuming no array formulae):

After

With ws.Range("A1:AW22")

enter the line

.Value = .Value



In article ,
mydogpeanut wrote:

1) the numbers I am pulling into the spreadsheets are being pulled in from
an excel ad in and comes in as text and I need to "convert to number" in
order for the conditional formatting to work. Is there a macro that can be
written to do this?

2) With the conditional formatting macro provided below is there a way to
specify what cells in the worksheets you want changed? For example A1:AW22?

Thanks!

"JE McGimpsey" wrote:

One way:

Public Sub FormatSmallValuesWhite()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws.Cells
.FormatConditions.Delete
With .FormatConditions.Add( _
Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:="-499999", _
Formula2:="499999")
.Font.Color = RGB(255, 255, 255)
End With
End With
Next ws
End Sub