(VBA) Custom Conditional Format Using Blanks & Not Between

[jjlafond]

Hello,

I have a large range of cells that I want to apply Conditional Formatting to as follows:

If not blank & value is not between L6 and M6, then fill red.

I would like to do this using a single format, perhaps using a custom formula?
I will be putting it in VBA, but with a working formula or other method I could record a macro.

 

[NeonRedSharpie]

Dim redRange As Range

Set redRange = Range(Cells(9, 12), Cells(31, 12))


    For Each valueCell In redRange
        If valueCell <> "" And valueCell < Cells(6, 12) Or valueCell > Cells(6, 13) Then
              MsgBox "Red"
        End If
    Next

I tried playing with conditional formatting but I can never figure it out. Here's a vba code to run it. Change the redRange cells to your range.

 

[jjlafond]

Thanks NRS, I made a few modifications to condense the code:

The vba for the formatting is what Im struggling with too.
This code WORKS, if and only if I start ad C19 every time:

Is there a way to re-write/modify this so that I can have a working command button to run this WITHOUT having to go in and modify the C19 every time??
The C19 seems to mark as a starting position, but I don't start at the same cell every time.

ActiveCell.Offset(1, 0).Select
ActiveCell.Resize(6, NumCavLines).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(LEN(TRIM(C19))>0,OR(C19>$L$6,C19<$M$6))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

 

[NeonRedSharpie]

Replace C19 with a variable. Let's call it startCell. You'll have the declare it as a string up where you declare your cells. Then, at some point, get a user print for the cell reference.

[COLOR=#000080][FONT=Courier New] 
     startCells = InputBox(Prompt:="Starting cell here please.", _[/FONT][/COLOR]
          Title:="Starting Cells", Default:="Type cell here")
                  If strName = "Type cell here" Or _
           strName = vbNullString Then           
                    Exit Sub [COLOR=#000080][FONT=Courier New]        
      Else
            ' Do whatever you want to do.
[/FONT][/COLOR]

 

[jjlafond]

Is there any way to find the startCell automatically instead of a prompt? Perhaps identify the active cell and make that the variable name?

 

[NeonRedSharpie]

    Dim myRow As String
    Dim myCol As String
    
    Dim startCell As String
    
    myRow = ActiveCell.Row
    myCol = ActiveCell.Column
    
    startCell = "Cells(" & myRow & "," & myCol & ")"

This will return the active cell location in a format that can be used in formulas.