Hello,

I would like to write a macro that goes into all the tabs in a work book and
conditional formats the cells to white font if the numbers are equal to or
between negative 499,999.00 and positive 499,999.00 can this be done?

thanks so much.

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

In article ,
mydogpeanut wrote:

Hello,

I would like to write a macro that goes into all the tabs in a work book and
conditional formats the cells to white font if the numbers are equal to or
between negative 499,999.00 and positive 499,999.00 can this be done?

thanks so much.

Works great thank you very much!

A couple of follow up questions...

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

In article ,
mydogpeanut wrote:

Hello,

I would like to write a macro that goes into all the tabs in a work book and
conditional formats the cells to white font if the numbers are equal to or
between negative 499,999.00 and positive 499,999.00 can this be done?

thanks so much.

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