VBA If statement criteria

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
864
Office Version
  1. 365
Platform
  1. Windows
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".


VBA Code:
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,204
Office Version
  1. 365
Platform
  1. Windows
Perhaps
VBA Code:
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

Well-known Member
Joined
Jul 14, 2011
Messages
1,950
Office Version
  1. 2016
May be:
VBA Code:
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

Well-known Member
Joined
Jun 11, 2014
Messages
864
Office Version
  1. 365
Platform
  1. Windows
Hi Micro,

I appreciate that you responded.

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

Thanks!
pinaceous
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
864
Office Version
  1. 365
Platform
  1. Windows
May be:
VBA Code:
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

Well-known Member
Joined
Jun 11, 2014
Messages
864
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 11, 2014
Messages
864
Office Version
  1. 365
Platform
  1. Windows
I tried to loop it, but it is not working.
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
864
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,181,678
Messages
5,931,369
Members
436,788
Latest member
er19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top