1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel macro w/ IsNumber() and conditional formatting

Discussion in 'Software Development' started by powerchordpunk, Oct 15, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. powerchordpunk

    powerchordpunk Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    2
    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:
    Code:
    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
    
     
  2. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    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:
    Code:
    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
    
     
  3. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    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)"
     
  4. powerchordpunk

    powerchordpunk Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    2
    works splendid! I learned a lot here thanks.
     
  5. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    No worries, please mark this thread as complete.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Excel macro IsNumber()
  1. Radheshyam
    Replies:
    0
    Views:
    365
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/759499

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice