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

sensiwood

New Member
Joined
Nov 28, 2016
Messages
3
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Welcome to the forum!

No, interior colors set manually do not trigger events.

It would be best to run a macro as needed. I would just use interior colors for the 5 grey cells as well. For various color routines, see: Color Functions In Excel
 
Upvote 0
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

why is the cell green?

as stated, you can not base formulas on manually colored cells. However, if you are logically deciding why a cell should be green then we can write some CF formulas to change it green and the others grey.
 
Upvote 0
Green is only for example. But yes, I want to change the cell background color in green manually, then I hoped that this event can trigger an action to set the adjacent right cells in another color (e.g. grey).
I failed to achieve it by conditional formatting. As discussed here, it seems CF cannot do this really...

Anyway thanks a lot.
 
Upvote 0
Manually changing a cells interior color will not trigger a event.

Why not do this:

Enter the value "G" into a cell and then we can have a sheet event script change that cells interior color to "Green" and then the adjacent 5 cells to the right to some other color.

But you would have to tell us what color you want the cells to be and what column will you be entering "G" into.

And this would be done with Vba.
 
Upvote 0
If you want to choose your colors later we could tell you how to do that.
And we can have the script remove the "G" after filling the cells with your color.
 
Last edited:
Upvote 0
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.
Code:
[table="width: 500"]
[tr]
	[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)
    End If
  End If
  Set LastSelection = Target
End Sub[/td]
[/tr]
[/table]

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.
 
Last edited:
Upvote 0
That looks like a good plan. I did not think about doing it this way.
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.
Code:
[TABLE="width: 500"]
<tbody>[TR]
[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)
    End If
  End If
  Set LastSelection = Target
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top