VBA If statement criteria

[Pinaceous]

Hi All,

I'm working on an If statement criteria now where, if Column F has a value and Column B is blank; Column B's cell produces an interior red color cell with the text "1299".

Private Sub ColumnB_ChangeF(ByVal Target As Range)


Dim n As Long

n = Target.row

If Range("B" & n).Interior.ColorIndex = xlNone _
    And Range("B" & n) = "" _
    And Range("F" & n) <> "" Then
  
        Range("B" & n).Interior.ColorIndex = 3
        Range("B" & n) = "1299"

    End If
  
  

End Sub

I would like to add a second criteria; were if the user types over the Column B interior cell color red with this value of "1299" to, for example "1234" (anything else but "1299"!) it will clear the interior color red cell for that Column B's cell.

I'm stomped!

Please help me, if you can!

Thanks,
pinaceous

 

[Micron]

Perhaps

If Range("B" & n).Interior.ColorIndex = xlNone And Range("B" & n) = "" _
  And Range("F" & n) <> "" Then
  Range("B" & n).Interior.ColorIndex = 3
  Range("B" & n) = "1299"
Else
   Range("B" & n).ClearFormats
End If

Not sure if that would work, as the format would clear regardless of which of your tests is false. So if the color can be green, or the range Bn is not "" or Fn is not "" and that is OK, then the format would be cleared.

 

[bebo021999]

May be:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
If Intersect(Target, Union(Columns(2), Columns(6))) Is Nothing Or Target.Count > 1 Then Exit Sub
n = Target.Row
If Range("B" & n).Interior.ColorIndex = xlNone And Range("B" & n) = "" And Range("F" & n) <> "" Then
        Range("B" & n).Interior.ColorIndex = 3
        Range("B" & n).Value = "1299"
        Exit Sub
End If
If Target.Column = 2 And Target.Interior.ColorIndex = 3 And Target.Value <> 1299 Then Range("B" & n).Interior.ColorIndex = xlNone
End Sub

This code wilee be placed in worksheet module, not general module

 

[Pinaceous]

Hi Micro,

I appreciate that you responded.

How do I change your code to not "clearformats" but still do that job?

Thanks!
pinaceous

 

[Pinaceous]

May be:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
If Intersect(Target, Union(Columns(2), Columns(6))) Is Nothing Or Target.Count > 1 Then Exit Sub
n = Target.Row
If Range("B" & n).Interior.ColorIndex = xlNone And Range("B" & n) = "" And Range("F" & n) <> "" Then
        Range("B" & n).Interior.ColorIndex = 3
        Range("B" & n).Value = "1299"
        Exit Sub
End If
If Target.Column = 2 And Target.Interior.ColorIndex = 3 And Target.Value <> 1299 Then Range("B" & n).Interior.ColorIndex = xlNone
End Sub

This code wilee be placed in worksheet module, not general module

Hello bebo021999,

That did the trick! I appreciate that you chimed in!

Respectfully,
pinaceous

 

[Pinaceous]

Hello bebo021999,

In testing out your code, I would like you to add something, if possible.

After your sub is run, and if the user tries to delete the Column B interior red cell with the text "1299" it will disappear.

I would like it, so that after your sub is run, if the Column B interior red cell with the text "1299" won't be deleted, if the user tries to delete this value, if it is still the original criteria.

Please let me know, if you need a screen shot for explanation.

Many thanks again!
pinaceous

 

[Pinaceous]

I tried to loop it, but it is not working.

 

[Pinaceous]

I tried to loop it, but it is not working.

Actually its okay!

But now how do I add, if the value of Column F value gets deleted and there is a Column B interior red value of "1299" that it will be cleared?