Change cell color

0

Hi,

I have 10 rows in excel. Row A1 Cell is Yellow color. If any other row b/w A2 to A10 is yellow then both yellow rows should be changed to Red. Say if A5 is yellow then both A1 and A5 cell color should be changed to Red. Please advice if this can be done.

Answer
Discuss

Answers

0

 The code below will do what you want. Install it on the code sheet of the worksheet on which you wish to see the action, then save the workbook as macro-enabled (xlsm format).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 12 Nov 2017
    
    ' change the range as required:-
    Const TestRange As String = "A1:A10"
    
    Static PrevCell As Range
    Dim Rng As Range
    Dim Cell As Range
    Dim RedRng As Range
    Dim i As Integer
    
    On Error Resume Next
    ' an error will occur if more than 32000 cells are selected
    i = Target.Cells.Count
    If Err Then Exit Sub
    
    On Error GoTo 0
    i = 0
    Set Rng = Range(TestRange)
    If PrevCell Is Nothing Then Set PrevCell = Rng.Cells(1)
    If Not Application.Intersect(PrevCell, Rng) Is Nothing Then
        For Each Cell In Rng
            With Cell.Interior
                ' change red back to yellow
                If .Color = vbRed Then .Color = vbYellow
                If .Color = vbYellow Then
                    If RedRng Is Nothing Then
                        Set RedRng = Cell
                    Else
                        Set RedRng = Application.Union(RedRng, Cell)
                    End If
                    i = i + 1
                End If
            End With
        Next Cell
        
        If i > 1 Then RedRng.Interior.Color = vbRed
    End If
    Set PrevCell = Target.Cells(1)
End Sub

The code will run after you have clicked on any cell in the test range. (You can change the test range in the first line of the code.) So, if you select a cell and change its colour to yellow nothing will happen until you click another cell anywhere on the worksheet. In order to avoid having the code slow down your worksheet unduly colours will only be changed if the previous click was within the test range. This is presuming that you would have to click on a cell in order to change its colour.

The code will not work if the cell colour is changed by conditional formatting.

Discuss

Discussion

Thank you so much. It works with little change.

I kept the code under
Sub MySelectAll()
End sub
senthil14 Nov 12, '17 at 11:54 am
I'm glad my suggestion works for you. Please mark the answer as "accepted" as a guide to others and reward to me. :-)
Variatus (rep: 1378) Nov 12, '17 at 8:17 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login