How to create a Reset instead of clear all button

[BlessedN2N]

I'm changing the background color of cells from black to green when clicked once. I can change the background cell colors from green to black when I right click.

Is there a way to create a Reset button that will change all the changed cell colors back to black from green when clicked instead of having to right click every cell individually or clearing all information from the cells. I found a reset button feature but it cleared all of the information instead of only changing the color back to black from green.

This is what i'm using

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
Case xlNone, 4: Target.Interior.ColorIndex = 4
Case Else: Target.Interior.ColorIndex = 4
End Select
End Sub




Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.Color = vbBlack
End Sub

 

[Mister H]

Hi and Welcome To The Board

You can try this macro I just recorded using the macro recorder. Make sure you try it on a COPY of your sheet Also have a look at this link. You can replace Colurs using Find and Replace

https://excel.tips.net/T002396_Finding_Cells_Filled_with_a_Particular_Color.html

Here is the code you can try

Sub Change_Green_To_Black()
    With Application.FindFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65280
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 0
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

Good Luck
Mark

 

[BlessedN2N]

It works!!!! Thanks Mark!!!