Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Conditional Formatting Using VBA

    I have the following code to format data using CF. Where the value in Col J from row 12 onwards is >=30, then I need , to highlight from column A to J and 2 rows below the value conating a value in Col J that is 30 and greater

    For Eg if J12 is say 35 then A12 to A14 must be highlighted in Gray and so on i.e for each value that is >=30 Col A to J + 2 rows below the column containing a value >= 30 must be highlighted using CF

    For some unkown reason, this is not happening

    Sub conditional_formating()
    Sheets("Sheet1").Select
    Finalrow = Range("a12").End(xlDown).Row
    With Range("A12:J" & Finalrow)
    .Select
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$J12>=30"
    .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    .Resize(2).FormatConditions(1).Interior.ColorIndex = 15
    End With
    End Sub

    Attached please find workbook

    Sample Data before CF
    Sample Data after CF (What the data should look like after CF)

    your assistance will be most appreciated
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    This should work.
    Code:
    Option Explicit
    
    Sub Conditional_Formating()
    
      Dim zChkCell As String
      Dim rngNext  As Range
      Dim rngCur   As Range
      
      Set rngCur = Range("A12")
      Sheets("Sheet1").Select
    
      While rngCur.Text <> "No. of Job Cards"
         Set rngNext = rngCur.End(xlDown)
         Debug.Print "Cur:" & rngCur.Address & " Next:" & rngNext.Address
         zChkCell = rngCur.Offset(0, 9).Address(xlA1)
         Debug.Print "Cur:" & rngCur.Address & " Next:" & rngNext.Address
         With Range(rngCur, rngNext.Offset(-2, 9))
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlExpression, Formula1:="=" & zChkCell & ">=30"
    
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
             .Resize(2).FormatConditions(1).Interior.ColorIndex = 15
         End With
        Set rngCur = rngNext
        
       Wend
       
    End Sub
    Last edited by RetiredGeek; 2011-09-12 at 22:31.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the help, much Appreciated. Code works perfectly

Posting Permissions

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