If a cell is green, fill adjacent cells background color on th right side automatically

[sensiwood]

Hi,

I am just seeking the method to achieve following scenario.
When setting a cell as green, the adjacent 5 cells' background color on the right side will be filled grey automatically.
This rule would be expected to apply to the whole worksheet.

Is it possible to do this in conditional formatting?
Thanks a lot in advance!
Ronnie

 

[Kenneth Hobson]

Sub GreenToH5Grey()
  Dim c As Range, r As Range
  For Each c In ActiveSheet.UsedRange
    '5287936=a green, 12566463 (a grey)
    If c.Interior.Color = 5287936 Then _
      c.Offset(, 1).Resize(, 5).Interior.Color = RGB(191, 191, 191)
  Next c
End Sub

 

[RCBricker]

not to say your guys' codes are great, but I still think this is a matter for Conditional Formatting. If the OP can tell us why he changes the color to green we can work out the CF formulas.