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.
    sample.xlsx

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,779
    Articles
    0
    Excel Version
    365
    try:
    Code:
    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)
      .AutoFilter
    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
    Hi,

    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
  •