Color Cells Automatically..

[newbie1234]

Hi guys,

I have this scenario which i need to automate it. I have 5 colums P1,P2,P3,P4,P5. It contains W or L combinations.

In my Column F i Concatenate(A2,B2,C2,D2) together and it follows on for the rest.

Now i have some criteria which i am going to search in my Column G. In Cell I1 i will list my combination to search. It can be as follows :

WWWW
LLLL
WWW
LLL

So what i need to do is search my F column for this possible match. If it is a match I need to color the my Player 1 to Player 5 for that match. I have shown in my example inside the excel. I know the formula for how to find for matches using my combination. But i got no idea on how i can color it with the respective colors automatically.

Yellow = all four W
Red= all four L
Green= will have 3 W and one L
Blue = have three L and one W

Will really appreciate if someone can help me out with this..

[Leith Ross]

Hello newbie1234,

Which cells are to be colored? The matching cells in column "F"?

Sincerely,
Leith Ross

[newbie1234]

Hello newbie1234,

Which cells are to be colored? The matching cells in column "F"?

Sincerely,
Leith Ross

If there is a match in column F.. You need to color that row Cells (A,B,C,D,E).

I have stated in my example on the top of my excel how it should be colored..

Thanks alot

[Leith Ross]

Hello newbie1234,

Sorry for delay. My cats were wanting to play with the computer cables. It's late and they're bored. Anyway, the following macro is attached to a button on "Sheet1". Click this and it color the rows that match what is in "I1", i.e. the "WWWW", "LLLL", "WWW", and "LLL" combinations.

Sub ColorCell()

  Dim Cell As Range
  Dim ColorArray(1 To 4)
  Dim I As Integer
  Dim Rng As Range
  Dim S As String
  
    With Worksheets("Sheet1")
      S = .Range("I1")
      Set Rng = .Range(.Cells(2, "F"), .Cells(.Rows.Count, "F").End(xlUp))
        For I = 1 To 4
          ColorArray(I) = .Cells(I + 1, "P").Interior.ColorIndex
        Next I
        For Each Cell In Rng
          A = Cell.Address
          If Cell = S Then
            Select Case Cell
              Case "LLL"
                Cell.Offset(0, -5).Resize(, 5).Interior.ColorIndex = ColorArray(1)
              Case "WWW"
                Cell.Offset(0, -5).Resize(, 5).Interior.ColorIndex = ColorArray(2)
              Case "LLLL"
                Cell.Offset(0, -5).Resize(, 5).Interior.ColorIndex = ColorArray(3)
              Case "WWWW"
               Cell.Offset(0, -5).Resize(, 5).Interior.ColorIndex = ColorArray(4)
            End Select
          End If
        Next Cell
    End With
   
End Sub

Sincerely,
Leith Ross

[newbie1234]

Thank you so much Leith for you help..

I tried running the Macro it works.. But i face 1 problem.. When i Enter WWWW and Hit the button it will color the cells accordingly. But when i enter LLLL combination and hit the button there isnt any action?

How can i rectify this issue..?

Thanks

[newbie1234]

Thank you so much Leith for you help..

I tried running the Macro it works.. But i face 1 problem.. When i Enter WWWW and Hit the button it will color the cells accordingly. But when i enter LLLL combination and hit the button there isnt any action?

How can i rectify this issue..?

Thanks

Problem Solved..Thanks Leith for helping out..