Excel conditional formatting - how to make exceptions?

[Highlander]

Hello all.

I've got a VBScript that creates an XLS file. In it I want to
implement conditional formatting; where if a cell value in Column C is
greater than 50000, then that cell font will be changed to bold and
red.

Recording a macro gave me this:

Sub Bold_Red()
Columns("C:C").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="50000"
With Selection.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3
.Italic = False
End With
End Sub

Then, after adding these Constants:

Const xlCellValue = 1
Const xlGreater = 5

I transposed the above macro into the following code:

'~~ Insert Bold and Red Font for any value
'~~ greater than 50000 in Column C
objExcel.Range("C:C").Select
objExcel.Selection.FormatConditions.Delete
objExcel.Selection.FormatConditions.Add xlCellValue, xlGreater,
"50000"
With objExcel.Selection.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3 'Red
.Italic = False
End With

The code works fine except for one problem. Some of the cells in
Column C contain text, not a number, and the text is getting changed
to the Bold and Red font. I want the font to change only for cells
that contain a number, not text. How do I exclude the text cells from
the conditional formatting?

Any suggestions would be greatly appreciated. Thanks!

- Dave

 

[steve_doc]

Possible solution
Assumes there are no blank cells


Sub Test50000()

Dim wb as Workbook
Dim ws as Worksheet
Dim rg as Range

Set wb = ThisWorkbook
Set ws = wb.WorkSheets("yourWorksheetName")
Set rg = ws.Range("C1") 'as example

Do Until IsEmpty(rg)
If rg.Value > 50000 Then 'assuming that formatted as general
rg.Font.Bold = True
Rg.Font.ColouriNdex = 3
Set rg = rg.Offset(1,0)
Else
Set rg = rg.Offset(1,0)
End If
Loop

End Sub

 

[Tom Ogilvy]

objExcel.Range("C:C").specialCells(xlConstants,xlNumbers).Select

change xlConstants to xlformulas if the numbers are produced by formula.

If it is a mix, do it twice - once with xlconstants and once with xlformulas
- or form a union

 

[Highlander]

objExcel.Range("C:C").specialCells(xlConstants,xlNumbers).Select

change xlConstants to xlformulas if the numbers are produced by formula.

If it is a mix, do it twice - once with xlconstants and once with xlformulas
- or form a union

Tom I tried your suggestion but couldn't get it to work.

Using a variation on steve_doc's suggestion, I solved my problem. I
did have to list the specific cells in the range; if I used the entire
column C it would be an infinite FOR loop.

Here's my corrected code that works:

'~~ Insert Bold and Red Font for any value
'~~ greater than 50000 in Column C
Set objRange = objExcel.Range("C3:C13")
For Each item in objRange.Cells
v = item.Value
Select Case True
Case IsNumeric(v) ' Determine if cell value is a number
IF v > 50000 Then
With item.Cells.Font
.Bold = True
.ColorIndex = 3 ' Red
.Italic = False
End With
End IF
End Select
Next

Thanks to both of you for responding!

- Dave

 

[Tom Ogilvy]

If you want to run the macro everytime you need to color the cells - then
that is the easiest - but then why did you attempt to use conditional
formatting.

This worked fine for me by the way. (just so you don't lead others astray).

Sub BBB()
'~~ Insert Bold and Red Font for any value
'~~ greater than 50000 in Column C
Set objExcel = Application
objExcel.Range("C:C").Select
objExcel.Selection.FormatConditions.Delete
objExcel.Range("C:C").SpecialCells(xlConstants, xlNumbers).Select
objExcel.Selection.FormatConditions.Add xlCellValue, xlGreater, 50000
With objExcel.Selection.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3 'Red
.Italic = False
End With

End Sub

Just like you had to define the constant arguments for the conditional
formatting, you would have to define them for the SpecialCells as well - but
seemed like you would know that since you already did it for data validation.