VBA Script for Onclick Color Formatting

**Solar** · 12-01-2009, 05:26 PM

Hey Guys,

I need help making a simple script that will allow me to single click a cell (In column D only) and change the background color of the 2 cells to the right of the target. So if I click D4 then E4 and F4 will change to light yellow for example. I have a script that performs this function but it works in any cell on the worksheet. I need to limit this to single columns. Thanks in advance!

-Sol

**rwgrietveld** · 12-01-2009, 05:38 PM

Look at intersect. The example in the help is very good. You can copy paste it.

**Solar** · 12-01-2009, 05:53 PM

I'm not sure where this example is located... could you provide a link?

**Solar** · 12-01-2009, 07:37 PM

Maybe someone could give a little more detail... The cells I want to use this on all contain hyperlinks. A code that would change cell format based on activating a hyperlink may be even better than an intersect code. Examples would be great everyone. Thanks.

**shg** · 12-01-2009, 11:15 PM

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 4 Then Intersect(Target.EntireRow, Columns("E:F")).Interior.ColorIndex = 36
End Sub

Code goes in the appropriate Sheet module:

Adding Code to a Sheet module
1. Copy the code from the post
2. Right-click on the tab for the relevant sheet and select View Code. This opens the Visual Basic Editor (VBE) and shows the object module for the selected worksheet.
3. Paste the code in the window
4. Press Alt+Q to close the VBE and return to Excel

**Leith Ross** · 12-02-2009, 01:06 AM

Hello Solar,

You can add this macro to the worksheet that has the hyperlinks in column "D". It will color the cells in columns "E" and "F" in the same row as the hyperlink when the hyperlink is activated.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  If Target.Range.Column = 4 Then
     Target.Range.Offset(0, 1).Resize(1, 2).Interior.ColorIndex = 36
  End If
End Sub

**Solar** · 12-02-2009, 12:46 PM

These are both great macros guys. Thanks a bunch. One final question. What would be the correct language to incorporate a specific range in column 4? Such as If the target is D3:D19 then it would run the script and do nothing on the other cells in the column. Thanks again.