# Excel macro w/ IsNumber() and conditional formatting



## powerchordpunk (Oct 15, 2008)

I have an excel macro that highlights cells based on a number entered from 0 to 100. I can highlight the area I want formatted automatically and run the macro, but any cell with a blank or non-numeric value gets formatted red (or sometimes interpreted as >100). I want to add a fourth condition either at the start or as a final condition to not change the cell formatting if NaN or alternatively format the background as white (no cell background color). Using excel 2003, i understand I am limited to 3 conditions for formatting. I am also unfamiliar with implementing the IsNumber function. Here is the code, captured w/ macro recording:

```
Sub proquiz()
'
' proquiz Macro
' Highlight all cells in range according to pass criteria
'
' Keyboard Shortcut: Ctrl+h
'
With Selection.Interior
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="100"
    Selection.FormatConditions(1).Interior.ColorIndex = 33
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="86", Formula2:="89"
    Selection.FormatConditions(2).Interior.ColorIndex = 45
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="85"
    Selection.FormatConditions(3).Interior.ColorIndex = 3
End With
End Sub
```


----------



## MRdNk (Apr 7, 2007)

Hi powerchordpunk,

Firstly, welcome to the forums.

Are you using this Macro to let us know your formatting conditions, or do you actually use this macro on a particular selection to highlight them?

If it's the latter, the best thing would be to ignore the conditional formatting and write a completely new Macro / procedure that does the same thing.

However, this is what you've actually asked for:

```
Sub proquiz()
'
' proquiz Macro
' Highlight all cells in range according to pass criteria
'
' Keyboard Shortcut: Ctrl+h
'
Dim t As Variant

t = ActiveCell.Address(False, False, xlA1, False, False)


    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=If(IsNumber(" & t & ")=TRUE,if(" & t & ">=100,True,False),False)"
    Selection.FormatConditions(1).Interior.ColorIndex = 33
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="86", Formula2:="89"
    Selection.FormatConditions(2).Interior.ColorIndex = 45
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(" & t & "<=85,IF(IsNumber(" & t & ")=TRUE,TRUE))"
    Selection.FormatConditions(3).Interior.ColorIndex = 3
End Sub
```


----------



## MRdNk (Apr 7, 2007)

Ps. There may be a better way to call the ActiveCell / Selection; but I don't know how.

And I have to say, I've learnt something new from this problem.
"ActiveCell.Address(False, False, xlA1, False, False)"


----------



## powerchordpunk (Oct 15, 2008)

works splendid! I learned a lot here thanks.


----------



## MRdNk (Apr 7, 2007)

No worries, please mark this thread as complete.


----------

