Use AND Formula in VBA

[tatertot]

I created a conditional format in my report to fill the cell in red when this formula = TRUE.

=AND(B16<>B17,B16<>"",B17<>"") How do I apply this into a macro? I am adding a validation button that will confirm that this AND formula has been corrected. People will see the red but sometimes keep right on working through the report. I want the validation to stop them and make them change the cells value so the AND formula = FALSE. I have zero idea where to start. The cell I want to "validate" is "B16". Thanks all!!!

<tbody>
</tbody>

 

[ChrisM]

You can add the following code to your validation button code:

with  sheets("yoursheetname")
If .range("B16") <> .range("B17) then
      msgbox "B16 not equal to B17"
      .range("B16").select
End If

If .range("B16") = "" Then
   msgbox "B16 is empty"
   .range("B16").select
End If

'repeat for B17
End With

Of course, this doesn't really stop the user from proceeding, you'd have to stop your validation code and not let them proceed if one of these errors pops up, perhaps by using "exit sub" after the msgbox

 

[cerfani]

if you want to see how to write the code, you can record a macro and set the conditional format. Then stop recording and check the code.

When you record code it will write the code based on ranges you select and how you manipulate the view of the sheet by scrolling, etc... then you can edit the recorded code to just reference the specific ranges you want and you can remove all the selecting and activate stuff and scrolling code.

 

[Roderick_E]

When you say stop them? Do you want a popup to keep pestering them until they complete B16?
if so, go to the sheet where they are completing this data, right click, VIEW CODE then paste this in.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("b16") <> Range("b17") And Range("b16") <> "" And Range("B17") <> "" Then
MsgBox "Validate message - for B16" ' change to whatever
Exit Sub
End If
End Sub

 

[tatertot]

Hi Roderick, when I use this code and attempt to run in VBA, it populates the Macro window to select a macro to run. Is this user error?

 

[tatertot]

Hi Cerfani,

I did that but could not set the recorded macro into an if then statement to generate a message box if the formula = true. Any advice?

 

[Roderick_E]

you don't run my code, it triggers every time the sheet is changed. meaning it will keep triggering everytime the user inputs/changes something in the sheet until the criteria/conditions aren't met

 

[cerfani]

Your code is only going to set datavalidation on a range. You will not test any condition. When you set the data validation object on a range. One property of the data validation object is where you describe the boolean test

can you paste your recorded code here

Hi Cerfani,

I did that but could not set the recorded macro into an if then statement to generate a message box if the formula = true. Any advice?

 

[Roderick_E]

meaning to test it, just type something in the sheet where you put it and if your B16 cell is red, it will trigger a popup

 

[cerfani]

Look at this, I recorded this with A1 selected...

Sub Macro1()'
' Macro1 Macro
'


'
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=AND(B1<>C1,B1<>"""",C1<>"""")"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "NOPE!!"
        .InputMessage = ""
        .ErrorMessage = "NOPE! NOPE! NOPE!"
        .ShowInput = True
        .ShowError = True
    End With
End Sub

change to...

Sub Macro1()'
' Macro1 Macro
'


'
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=AND(B1<>C1,B1<>"""",C1<>"""")"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "NOPE!!"
        .InputMessage = ""
        .ErrorMessage = "NOPE! NOPE! NOPE!"
        .ShowInput = True
        .ShowError = True
    End With
End Sub

that is a simple example... then you can run the subroutine in an event or whenever you want, you can make a button or whatever for it

you can write a macro that will set any cells you want with any kind of validation you want

dont forget to set ignoreblank to true or false depending what you need to do