Results 1 to 3 of 3

Thread: macro to fill color when criteria matches

  1. #1

    macro to fill color when criteria matches

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi champs,

    I have 5 col,

    Col A - Doc
    Col B - Pin
    Col C - Account
    Col D - Description
    Col E - Amount

    the Pin (col B) will have values 40 and 50 for some Doc numbers

    the criteria is if any document number (col A) has Pin (Col B) "40" or "50" with account (Col C) "2245007",

    then the entire lines of the doc number (Col A) has to be coloured. Pls refer the sample file attached. Thanks in advance.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Excel Version
    Sub blah()
    With ActiveSheet.Range("A2")
      .AutoFilter Field:=2, Criteria1:="=40", Operator:=xlOr, Criteria2:="=50"
      .AutoFilter Field:=3, Criteria1:="2245007"
      Set myrng = ActiveSheet.AutoFilter.Range.Columns(1)
      Set myAutoFilter = myrng
      Set myrng = myrng.Offset(1).Resize(myrng.Rows.Count - 1)
      Set myrng = myrng.SpecialCells(xlCellTypeVisible)
    End With
    For Each cll In myrng.Cells
      Set StartCell = Nothing: Set EndCell = Nothing
      If Len(cll.Value) > 0 Then Set StartCell = cll Else Set StartCell = cll.End(xlUp)
      Set EndCell = cll.End(xlDown).Offset(-1)
      Intersect(myAutoFilter, Range(StartCell, EndCell)).Interior.ColorIndex = 3
    Next cll
    End Sub

  3. #3

    Thanks for the code. works perfect. you saved my day.

Posting Permissions

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