+ Reply to Thread
Results 1 to 11 of 11

Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers??

Hybrid View

  1. #1
    Registered User
    Join Date
    03-23-2012
    Location
    West Coast
    MS-Off Ver
    Excel 2003, 2007, 2011
    Posts
    15

    Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers??

    I have a Macro that highlights cells that contain numbers using conditional formatting.
    Numbers <0.8 are highlighted in green
    Numbers >1.2 are highlighted in red

    But the problem is it treats texts, dates, and blanks as numbers.

    Any cell with text is highlighted in red.
    Dates written in Jan-10 format are highlighted in red.
    Blank cells are treated as zero and highlighted in green.

    How do I correct this so that only cells containing numerical values are highlighted accordingly?


    Your help will be greatly appreciated.

    Thanks in advance.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    are you doing this with a macro, or just standard conditional formating? if it is a macro could you include the code.

    also note, dates no matter how they are formated are actualy a number at the formula level, for example 10-Jan-2012 is actualy 40918.
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Registered User
    Join Date
    03-23-2012
    Location
    West Coast
    MS-Off Ver
    Excel 2003, 2007, 2011
    Posts
    15

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    I made this using Recorded Macro and Conditional Formatting. So there is no way to exclude the dates from this conditional formatting?

    Thanks

    Sub Macro3()
    '
    ' Macro3 Macro
    '
    
    '
        Cells.Select
        ActiveWindow.SmallScroll Down:=-12
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=1.2"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=0.8"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End Sub
    Last edited by Paul; 03-24-2012 at 12:54 AM. Reason: Added CODE tags for user. Please do this yourself in the future.

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    Recorded macro's are enharently unperfect, but give this a shot

    i added a few more paramaters to your conditional formating

    =AND(A1>1.2,A1<40000,T(A1)="",A1<>"")"

    A1>1.2 - Your goal
    A1<40000 - Makes sure its not a date
    T(A1)="" - Make sure its not text
    A1<>="" - make sure its blank




    Sub Macro3()
    '
    ' Macro3 Macro
    '
    
    '
        Cells.Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(A1>1.2,A1<40000,T(A1)="",A1<>"")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(A1<.8,T(A1)="",A1<>"")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End Sub

  5. #5
    Registered User
    Join Date
    03-23-2012
    Location
    West Coast
    MS-Off Ver
    Excel 2003, 2007, 2011
    Posts
    15

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    Thanks, but now it does not do anything from what I can tell. I'm running Excel 2010 if it matters.

  6. #6
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    code error.. try this

    Sub Macro3()
    '
    ' Macro3 Macro
    '
    
    '
        Cells.Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(A1>1.2,A1<40000,T(A1)="""",A1<>"""")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(A1<.8,T(A1)="""",A1<>"""")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End Sub

  7. #7
    Registered User
    Join Date
    03-23-2012
    Location
    West Coast
    MS-Off Ver
    Excel 2003, 2007, 2011
    Posts
    15

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    Works great.

    Thanks!

  8. #8
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    no problem

    note for future consideration: to use a " in VBA within a string, enter it as a "" that is what my error was in the first run of code.

    glad to have been able to help

  9. #9
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    no problem

    note for future consideration: to use a " in VBA within a string, enter it as a "" that is what my error was in the first run of code.

    glad to have been able to help

  10. #10
    Registered User
    Join Date
    03-23-2012
    Location
    West Coast
    MS-Off Ver
    Excel 2003, 2007, 2011
    Posts
    15

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    Thanks again! I was wondering how can I make the >1.2 to fall within 9.9? I do not want it to highlight anything greater than 9.9.

  11. #11
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Conditional Formatting for Numbers but text, dates, and blanks are treated as numbers?

    just change A1<40000 to A1<10 like this:

    Sub Macro3()
    '
    ' Macro3 Macro
    '
    
    '
        Cells.Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(A1>1.2,A1<10,T(A1)="""",A1<>"""")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(A1<.8,T(A1)="""",A1<>"""")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1