Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Conditional Formatting through VBA (03 SP 3)

    I am trying to find a better approach to formatting an unknown number of rows. I am using the following to conditionally format a column of cells. The problem is I don't know how to apply the formatting to the entire column AND don't know how to find the the last row in the column. (Please note that this is from an Access app which requires the "xlApp.")

    xlApp.Range("K2").Select
    xlApp.Selection.FormatConditions.Delete
    xlApp.Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=IF(AI2=""Yes"",TRUE,FALSE)"
    With xlApp.Selection.FormatConditions(1).Font
    .Bold = True
    .Italic = False
    .ColorIndex = 3
    End With
    xlApp.Selection.Copy
    xlApp.Range("K3:K51042").Select
    xlApp.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Any help is greatly appreciated.

    Ken

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting through VBA (03 SP 3)

    I forgot that I used the A column instead of the AI column when I developed the macro. My original post has been updated to show the correct column reference.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting through VBA (03 SP 3)

    <P ID="edit" class=small>(Edited by mbarron on 23-Mar-08 22:05. Got rid of an unnecessary ] and fixed column reference to match OP's post.)</P>I'm not sure where the xlApp's should go, so I didn't put them in. The following will determine the last row and then apply the conditional formatting based on row 2 through that last row.


    Dim i As Long, lRow As Long
    lRow = Cells(65536, 11).End(xlUp).Row <font color=448800> ' determines last row </font color=448800>
    Cells(2, 11).Select
    With Range(Cells(2, 11), Cells(lRow, 11))
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=AI2 = ""yes"""
    End With

    With Range(Cells(2, 11), Cells(lRow, 11)).FormatConditions(1).Font
    .Bold = True
    .Italic = False
    .ColorIndex = 3
    End With

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting through VBA (03 SP 3)

    Here is a slight variation. Like mbarron, I simplified the conditional formatting formula.
    <code>
    Dim lRow As Long
    lRow = xlApp.Range("K" & xlApp.Rows.Count).End(xlUp).Row
    xlApp.Range("K2:K" & lRow).Select
    With xlApp.Selection
    .FormatConditions.Delete
    With .FormatConditions.Add(Type:=xlExpression, _
    Formula1:="=(AI2=""Yes"")").Font
    .Bold = True
    .Italic = False
    .ColorIndex = 3
    End With
    End With</code>

Posting Permissions

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