Here is a possible way to automate what you want, but you will have to tolerate a delay until you select another cell on the same worksheet as the cell you manually colored green
. This is sheet event code, so it goes in the worksheet code module for the worksheet whose cells you will be coloring green. You did not tell us whether the cells to be colored green will be restricted to a particular area of the worksheet, so I made it work for any cell (except the rightmost five cells, of course)... if you want something different, you have to tell us in what way. Oh, by the way, the number I highlighted in red in the code is the value my system shows for the Green color... if your green color has a different color value, use your value in place of the red highlighted value in the code below.
[TD]Dim LastSelection As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not LastSelection Is Nothing Then
If LastSelection.Count = 1 Then
If LastSelection.Interior.Color = [B][COLOR=#ff0000]5287936[/COLOR][/B] Then LastSelection.Offset(, 1).Resize(, 5).Interior.Color = RGB(191, 191, 191)
Set LastSelection = Target
HOW TO INSTALL Event Code
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window (make sure that free-floating Dim statement is at the top of the module). That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.