Can you make Excel Change Another Cell's Colour When This Cell is Clicked?

Question

I've tried Googeling but found no answers... very frustrated.

I'm trying to do this in Excel:

1. Select cells B1 - B5 (or just B1)
2. The select cell A1 (which is yellow in colour)
3. Cells B1 - B5 (or just B1 if only B1 selected) also becomes yellow.

This is for a leaves (holiday) spreadsheet and there are different colours for different leave purposes (eg. sick, holiday, half day, etc). Every time someone highlights the cells on the spreadsheet calendar, they can just click on the corresponding colour to colour the selected cells. I am trying to replicate this same behaviour.

This was done by someone for our office's excel sheet but I cannot contact the original person who made. I'm trying to duplicate this function in a new excel sheet.

How do I do it? Macro? VB script?

Answer 1

You can only do this through VBA. Here is some code you can drop into the Sheet object.

Private lastRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        If Not lastRange Is Nothing Then
            lastRange.Interior.Color = Range("A1").Interior.Color
        End If
    Else
       Set lastRange = Target
    End If
End Sub

The lastRange object is saved from selection to selection. This is the only way to have VBA remember what range we want to highlight. Whenever you change selection, it sets that selection to lastRange unless it's A1 in which case it colors the previous selection as whatever color A1 is..

---

Based on your comments, though, your need is a bit broader than this. Since you want a range of cells of various colors, this is probably the code you want:

Private lastRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
            If Not lastRange Is Nothing Then
                lastRange.Interior.Color = Target.Interior.Color
                Exit Sub
            End If
        End If
    End If
    Set lastRange = Target
End Sub

Now there's a whole range of cells A1:A5 that can be all different colors. If you select a single cell in that range, it will set your previous selection to be the same color as that current cell. You can adjust the A1:A5 range to be whatever you need.

---

This has one oddity that I know of: If you select a range, then a shape, then A1, the range you had selected before is still stored as lastRange. That's probably OK but I don't know your use case.

Answer 2

Open your excel file and in the HOME tab go to Conditional Formatting and click on manage rules. You should be able to see a rule related to the cells mentioned in your question.

Check the below microsoft link for more info:

How to use conditional formatting