new to VBA Need help

[Perma]

I'm trying to make a button to change specific cell Color Depending on the Current Cell Color
Basically if I press one Specific button the Cell next to it will change Color depending on what color it currently has.
When the Cell is clear it Changes to yellow, if the cell is yellow it Changes to light blue, if the cell is light blue it changes to green and if it is green it clears out the cell color.
I have a general Idea on just changing the color with a button but I don't know how to set conditions.
any help will be very much appreciated.

 

[kevin9999]

Try this (assumes A1 is the cell you want to change - adjust to suit)

Private Sub Button1_Click()

    Select Case [A1].Interior.ColorIndex
        Case xlNone
            [A1].Interior.ColorIndex = 6
        Case 6
            [A1].Interior.ColorIndex = 8
        Case 8
            [A1].Interior.ColorIndex = 4
        Case 4
            [A1].Interior.ColorIndex = xlNone
    End Select
    
End Sub

 

[Perma]

Try this (assumes A1 is the cell you want to change - adjust to suit)

Private Sub Button1_Click()

    Select Case [A1].Interior.ColorIndex
        Case xlNone
            [A1].Interior.ColorIndex = 6
        Case 6
            [A1].Interior.ColorIndex = 8
        Case 8
            [A1].Interior.ColorIndex = 4
        Case 4
            [A1].Interior.ColorIndex = xlNone
    End Select
   
End Sub

sorry for the late reply Thank you this works wonders

 

[Perma]

Try this (assumes A1 is the cell you want to change - adjust to suit)

Private Sub Button1_Click()

    Select Case [A1].Interior.ColorIndex
        Case xlNone
            [A1].Interior.ColorIndex = 6
        Case 6
            [A1].Interior.ColorIndex = 8
        Case 8
            [A1].Interior.ColorIndex = 4
        Case 4
            [A1].Interior.ColorIndex = xlNone
    End Select
   
End Sub

making a new reply because it just dawned on me and I cannot find the edit button but is there a way where this can affect multiple cells individually? or do you have to copy paste the code everytime and just change the specified cell?

 

[kevin9999]

making a new reply because it just dawned on me and I cannot find the edit button but is there a way where this can affect multiple cells individually? or do you have to copy paste the code everytime and just change the specified cell?

How many cells do you want this to apply to, and which cells are they?

 

[Perma]

12 cells namely m2 down to m7, and n2 down to n7

 

[kevin9999]

That being the case, I'd use a BeforeDoubleClick event to cover all cells of interest at once. You put this code in the sheet code area of the sheet with the cells. Right click on the sheet tab name, select View Code. The window on the right of the screen is where you put the code. Save the file, and test it by double-clicking on one of the cells in the range M2:N7.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    If Not Intersect(Me.Range("M2:N7"), Target) Is Nothing Then
        cancel = True
        Select Case Target.Interior.ColorIndex
            Case xlNone
                Target.Interior.ColorIndex = 6
            Case 6
                Target.Interior.ColorIndex = 8
            Case 8
                Target.Interior.ColorIndex = 4
            Case 4
                Target.Interior.ColorIndex = xlNone
            Case Else
                Target.Interior.ColorIndex = Target.Interior.ColorIndex
        End Select
    End If
End Sub

 

[Perma]

That being the case, I'd use a BeforeDoubleClick event to cover all cells of interest at once. You put this code in the sheet code area of the sheet with the cells. Right click on the sheet tab name, select View Code. The window on the right of the screen is where you put the code. Save the file, and test it by double-clicking on one of the cells in the range M2:N7.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    If Not Intersect(Me.Range("M2:N7"), Target) Is Nothing Then
        cancel = True
        Select Case Target.Interior.ColorIndex
            Case xlNone
                Target.Interior.ColorIndex = 6
            Case 6
                Target.Interior.ColorIndex = 8
            Case 8
                Target.Interior.ColorIndex = 4
            Case 4
                Target.Interior.ColorIndex = xlNone
            Case Else
                Target.Interior.ColorIndex = Target.Interior.ColorIndex
        End Select
    End If
End Sub

this works great. I'm guessing you can't do it with a a button but thank you very much

 

[kevin9999]

this works great. I'm guessing you can't do it with a a button but thank you very much

You're welcome, and thanks for the feedback.