Tricky little Colour macro needed

[mikemcbain]

G'day Magicians

Can someone please help me with this one?

I have a macro that colours cells AF2:AF6 with lime green (65250) and also colours some of the cells in U2:U6

I want to add to that macro or create another macro that will look at those two columns and if there is a cell coloured in both columns on the same row will then colour those cells/row in column J ?

With thanks in anticipation

Mike.

 

[SteveO59L]

what code do you already have?

 

[mikemcbain]

Steve

Cells AF2:AF6 are permanently coloured and the piece of code selecting which cells in Col U will be similarly coloured is...

64: Next rw_v

For rw_w = 2 To rw_s
If Cells(rw_w, 21) = 0 Then
Cells(rw_w, 21).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65250
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next rw_w

...so that is selecting any cell containing a zero to be coloured.

And I am hoping to be able to auto colour any of those five cells in Col J where Cols U & AF are both coloured.

Cheers

Mike.

 

[Fluff]

How about

For rw_w = 2 To rw_s
   If Range("U" & rw_w).Value = 0 Then
      With Range("J" & rw_w & ",U" & rw_w).Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .Color = 65250
         .TintAndShade = 0
         .PatternTintAndShade = 0
      End With
   End If
Next rw_w

 

[mikemcbain]

Many thanks Fluff however that colours all the cells showing a zero in Col U instead of only those that are in rows 2 to 6 and so coloured in Col AF?

With thanks

Mike.

 

[Fluff]

Ok, how about

For rw_w = 2 To rw_s
   If Range("U" & rw_w).Value = 0 And rw_s < 7 Then
      With Range("J" & rw_w & ",U" & rw_w).Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .Color = 65250
         .TintAndShade = 0
         .PatternTintAndShade = 0
      End With
   Else
      With Range("U" & rw_w).Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .Color = 65250
         .TintAndShade = 0
         .PatternTintAndShade = 0
      End With
   End If
Next rw_w

 

[mikemcbain]

Thank you Fluff but no sorry that one is colouring the whole of column U and none of the Cells in Col J instead of just those sells in the rows that are coloured in both Cols U and AF?

Mike.

 

[Fluff]

how about

For rw_w = 2 To rw_s
   If Range("U" & rw_w).Value = 0 Then
      If rw_w < 7 Then
         With Range("J" & rw_w & ",U" & rw_w).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65250
            .TintAndShade = 0
            .PatternTintAndShade = 0
         End With
      Else
         With Range("U" & rw_w).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65250
            .TintAndShade = 0
            .PatternTintAndShade = 0
         End With
      End If
   End If
Next rw_w

 

[mikemcbain]

G'day again Fluff

Truly MAGICAL!

In lieu of the gift you deserve can I ask that you please take a moment to smell the roses from your garden (or Avatar)?

I remain indebted to you....

Mike.

 

[Fluff]

Glad to help & thanks for the feedback