Macro : Remove Green Color After Run Macro in Second

[muhammad susanto]

hi all..

i have code to filling mark "V" if cell contains background green color..
i want only the green color background is gone after i run macro in second...
how to make it in macro :

Sub Checklist_V()Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet
    For Each cell In .UsedRange
        If cell.Interior.ColorIndex = "14" Then cell.Value = "V"
    Next cell
End With
Application.ScreenUpdating = True
End Sub

any helps much appreciated...

 

[Trebor76]

This should do the trick:

Option Explicit
Sub Macro2()

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In ActiveSheet.UsedRange
        If rngMyCell.Interior.ColorIndex = 14 And rngMyCell.Value = "V" Then
            rngMyCell.Interior.Color = xlNone
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Note both conditions (an interior colour of 14 and a value of "V" (case sensitive)) have to be met for the background colour to be removed.

HTH

Robert

 

[muhammad susanto]

This should do the trick:

Option Explicit
Sub Macro2()

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In ActiveSheet.UsedRange
        If rngMyCell.Interior.ColorIndex = 14 And rngMyCell.Value = "V" Then
            rngMyCell.Interior.Color = xlNone
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Note both conditions (an interior colour of 14 and a value of "V" (case sensitive)) have to be met for the background colour to be removed.

HTH

Robert

thank you Robert.. i think not yet. the criteria :
1. i filled green color (manually filled) in cell then i run my macro so the result is mark "V" displayed.
2. now, when i click/ run macro again-in second), only background green color is gone not include mark "V"

perhaps..like turn on/off green color background...

 

[Trebor76]

So if you want to remove the background colour if its interior colour is 14 regardless if there's a "V" in the cell or not, just change this line of code...

If rngMyCell.Interior.ColorIndex = 14 And rngMyCell.Value = "V" Then

to this:

If rngMyCell.Interior.ColorIndex = 14 Then

Robert

 

[muhammad susanto]

So if you want to remove the background colour if its interior colour is 14 regardless if there's a "V" in the cell or not, just change this line of code...

If rngMyCell.Interior.ColorIndex = 14 And rngMyCell.Value = "V" Then

to this:

If rngMyCell.Interior.ColorIndex = 14 Then

Robert

hello Robert still not work that my expectation...
your macro make green color is gone only...
i have 2 request :
1. at first, i filled green color (manually filled) and after running my macro mark "V" is displayed;
2. after that using that macro (after modified) , my green color is gone but mark "V" still displayed...

how to join 2 codes in single code, perhaps, one code can do 2 task..

 

[Fluff]

Try this mod on the code supplied by Trebor76

Sub Macro2()

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In ActiveSheet.UsedRange
        If rngMyCell.Interior.ColorIndex = 14 And rngMyCell.Value = "V" Then
           [COLOR=#0000ff] rngMyCell.Clear[/COLOR]
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

 

[muhammad susanto]

thank Fluff, but all it is gone not that i want it..

 

[Fluff]

Is this what you're after?

Sub Checklist_V()

    Dim Cl As Range
    
    Application.ScreenUpdating = False
    With ActiveSheet
        For Each Cl In .UsedRange
            If Cl.Interior.ColorIndex = "14" And Not Cl.Value = "V" Then
                Cl.Value = "V"
            ElseIf Cl.Interior.ColorIndex = "14" And Cl.Value = "V" Then
                Cl.Interior.Color = xlNone
            End If
        Next Cl
    End With
    Application.ScreenUpdating = True
    
End Sub

 

[muhammad susanto]

Is this what you're after?

Sub Checklist_V()

    Dim Cl As Range
    
    Application.ScreenUpdating = False
    With ActiveSheet
        For Each Cl In .UsedRange
            If Cl.Interior.ColorIndex = "14" And Not Cl.Value = "V" Then
                Cl.Value = "V"
            ElseIf Cl.Interior.ColorIndex = "14" And Cl.Value = "V" Then
                Cl.Interior.Color = xlNone
            End If
        Next Cl
    End With
    Application.ScreenUpdating = True
    
End Sub

Yes...Fluff, thank you very much...its worked well...

 

[Fluff]

Glad we could help & thanks for the feedback