Excel conditional formatting - how to make exceptions?

Discussion in 'Microsoft Excel Programming' started by Highlander, Jun 19, 2007.

  1. Highlander

    Highlander Guest

    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
     
    Highlander, Jun 19, 2007
    #1
    1. Advertisements

  2. Highlander

    Guest Guest

    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

    > 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
    >
    >
     
    Guest, Jun 19, 2007
    #2
    1. Advertisements

  3. Highlander

    Guest Guest

    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

    --
    Regards,
    Tom Ogilvy


    "Highlander" wrote:

    > 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
    >
    >
     
    Guest, Jun 19, 2007
    #3
  4. Highlander

    Highlander Guest

    On Jun 19, 9:53 am, Tom Ogilvy <>
    wrote:
    > 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
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >


    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
     
    Highlander, Jun 19, 2007
    #4
  5. Highlander

    Guest Guest

    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.


    --
    Regards,
    Tom Ogilvy


    "Highlander" wrote:

    > On Jun 19, 9:53 am, Tom Ogilvy <>
    > wrote:
    > > 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
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >

    >
    > 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
    >
    >
     
    Guest, Jun 19, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Conditional Formatting w/ Conditional Functions... using AND

    Guest, Mar 16, 2004, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    396
    Frank Kabel
    Mar 16, 2004
  2. Guest

    Can I make a cell flash in conditional formatting?

    Guest, Feb 24, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    347
    Bob Phillips
    Feb 24, 2005
  3. Guest

    Conditional formatting based on formatting

    Guest, Nov 1, 2006, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    316
    Peter T
    Nov 1, 2006
  4. Guest

    How to make a cell blinking by conditional formatting?

    Guest, Jul 13, 2007, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    1,713
    Guest
    Jul 13, 2007
  5. Replies:
    0
    Views:
    408
Loading...

Share This Page