Undoing VBA color changes of a cell having Conditional Formatting

[Bwanna]

Hi and thank you ahead of time for helping me figure this out.

Situation:
-cell A1 has CF that imposes a gray fill color is the cell is BLANK
-Need to have VBA code that changes that color to Black based on certain conditions

Dilemma:
-When the VBA conditions are no longer, how do I get the CF to be reapplied for cell A1

Currently trying:
Code to Change Color of Cell:
-Range (A1).Interior.ColorIndex = 1
-Range (A1).FormatConditions.Delete

Code to Undo the VBA Color changing of Cell:
-Range (A1).Interior.ColorIndex = xlNone


-IS there a way to manually recycle the CF rules? i.e. reapply

I also noticed that after the VBA code "xlNone" is applied, NONE of the CF rules apply to cell A1 as a result of user entering new values into the cell (other CF rules apply different colors depending on values)

Thanks again,

 

[NdNoviceHlp]

I'm guessing this will return it to normal. Maybe stop using the xlNone thing and the CF will work again? Dave

Range (A1).Interior.ColorIndex =  xlAutomatic

 

[Bwanna]

I'm guessing this will return it to normal. Maybe stop using the xlNone thing and the CF will work again? Dave

Range (A1).Interior.ColorIndex =  xlAutomatic

Thanks for the reply. Tried it, but doesn't reapply the CF.

 

[Bwanna]

The original information was to simplify the request, but here's the planned code in case it helps explain

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("I6:I14")) Is Nothing And Target.Cells.Count = 1 Then
        ActiveSheet.Unprotect
            Dim i As Integer
            If Target.Value = "PASSTHRU" Then
                For i = 1 To 5
                    Target.Offset(0, i).Interior.ColorIndex = 1
                    Target.Offset(0, i).FormatConditions.Delete
                    Target.Offset(0, i).Locked = True
                Next i
            Else
                For i = 1 To 5
                    Target.Offset(0, i).Interior.ColorIndex = xlNone
                    Target.Offset(0, i).Locked = False
                Next i
            End If
        ActiveSheet.Protect
    End If
End Sub

The cell conditions in the Range (I6:I14) effect the 5 cells to the right (Fill BLACK). It's in undoing this that I'm having the challenge, either by selecting a different dropdown or manually deleting the contents of the cell... I can't seem to get the CF's to retake. Even when I select a dropdown in THOSE cells, the CF doesn't take.

 

[NdNoviceHlp]

Can't get the sheet protection to work but it seems like the rest works. Give it a trial. Dave

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("I6:I14")) Is Nothing And Target.Cells.Count = 1 Then
    'ActiveSheet.Unprotect
            Dim i As Integer
            If Target.Value = "PASSTHRU" Then
            
                For i = 1 To 5
                    Target.Offset(0, i).Interior.ColorIndex = 1
                    Target.Offset(0, i).FormatConditions.Delete
                    Target.Offset(0, i).Locked = True
                Next i
            Else
                For i = 0 To 5
                    Target.Offset(0, i).Interior.ColorIndex = xlColorIndexNone
                    Target.Offset(0, i).Locked = False
                Next i
            End If
    'ActiveSheet.Protect
    End If
End Sub

 

[Bwanna]

Can't get the sheet protection to work but it seems like the rest works. Give it a trial. Dave

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("I6:I14")) Is Nothing And Target.Cells.Count = 1 Then
    'ActiveSheet.Unprotect
            Dim i As Integer
            If Target.Value = "PASSTHRU" Then
            
                For i = 1 To 5
                    Target.Offset(0, i).Interior.ColorIndex = 1
                    Target.Offset(0, i).FormatConditions.Delete
                    Target.Offset(0, i).Locked = True
                Next i
            Else
                For i = 0 To 5
                    Target.Offset(0, i).Interior.ColorIndex = xlColorIndexNone
                    Target.Offset(0, i).Locked = False
                Next i
            End If
    'ActiveSheet.Protect
    End If
End Sub

THANKS, I'll try this later, for now I separated out all the Cell Fill formatting to CF and the Locking to VBA.