VBA If criteria

[Pinaceous]

Hi All,

I'm trying to provide a specific criteria, if I delete/remove a value in Column F and Column B has a color index of 3 with the text "1299" that it will return Column B to a color index of xlNone and clear the contents of the "1299", when the Column F value has been removed with its specific criteria.

I've managed to provide so far where if I delete the value in Column F that it clears the contents of the "1299" of Column B but I cannot remove the interior color of 3 to xlNone.

Here is a sample of my code that I'm working on:

Private Sub MessageColumnB(ByVal Target As Range)

    Dim n As Long

  n = Target.row

    
If Range("B" & n).Interior.ColorIndex = 3 _
    And Range("B" & n) = "1299" _
    And Range("F" & n) = "" Then
          
          Cells(n, "B").ClearContents
          
   'Cells(n, "B").Interior.ColorIndex = xlNone       =>? - How do I add this part to it?
    
    End If
    
    
End Sub

Many thanks in advance!

Pinaceous

 

[Pinaceous]

I realize that

Cells(n, "B").Clear

can be an option, but then how would I keep the formatting of the original cell?

 

[offthelip]

try this simple modification:

Private Sub MessageColumnB(ByVal Target As Range)

    Dim n As Long

  n = Target.Row

    
If Range("B" & n).Interior.ColorIndex = 3 _
    And Range("B" & n) = "1299" _
    And Range("F" & n) = "" Then
          
          Cells(n, "B").ClearContents
          Range("B" & n).Interior.ColorIndex = 0
   'Cells(n, "B").Interior.ColorIndex = xlNone       =>? - How do I add this part to it?
    
    End If
    
    
End Sub

 

[Pinaceous]

Hey offthelip,

Thank you for that suggestion! It works great! Would you know how I can also set up an additional criteria with Column B, where if a number value other than 1299 is entered into Column B that it will remove its interior color red back to xlNone and keep that value entered?

For example, if a user wants to override the Column B interior color red with its value of "1299" with a value between (1 to 2359 except 1299) with ie."1234" that it will remove the interior cell condition and keep that new value?

For example, if I have this condition below and I want to type over the "1299" value with 12:34 that it will provide this progression.

Please let me know, thanks!

 

[offthelip]

You could just use conditional fromatting on cell B2 to change the color of the cell.
However if you want to do it in vba try this:

Private Sub MessageColumnB(ByVal Target As Range)
 Dim n As Long
 n = Target.Row
   
If Range("B" & n).Interior.ColorIndex = 3 _
    And Range("B" & n) = "1299" _
    And Range("F" & n) = "" Then
          Cells(n, "B").ClearContents
          Range("B" & n).Interior.ColorIndex = 0
 End If
' add this bit
 If Range("B" & n) <> "1299" Then
          Range("B" & n).Interior.ColorIndex = 0
 End If
    
End Sub

 

[Pinaceous]

Thank you

offthelip!​