set Color format and Line style to 'active range'?

[SURFER349]

So i've got this code I'm trying to use to conditionally color format a sheet. I'm putting the color codes at the top to be adjusted easily and then referenced later. It looks for a keyword in column B then applies the format to the entire row.
This works all great and all, but it applies it to the entire sheet, like all columns down to row 1M...

• How can I adjust this to only apply to $A1:AA1000 or the 'active range' or 'current region'?

sub colorformatting()
'this sub only needs to be used once on a sheet, it adds conditional formatting that stick to the sheetSub ColorFormating()
Dim Bad As String
Dim Good As String
    Bad = "13551615" 'red
    Good = "13561798" 'green
    
'*NOTE* change the column Letter to reflect the 'Status' column.  Most use ColC, but StepMaster uses colB
With Cells
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=($B1=""DELETE"")"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = BAD 'this is the "BAD" color index
            .TintAndShade = 0

        End With
        StopIfTrue = False
    End With


    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=($B1=""NEW"")"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = Good 'this is the "GOOD" color index
            .TintAndShade = 0
        End With
        StopIfTrue = False
    End With
End With
End Sub

I'm also trying to add gridlines (All Borders) to these cells. How can I integrate this recorded script into the original?

Sub lines()

    Range("A1:AD6").Select
    Range("D4").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

 

[Fluff]

How about

Sub colorformatting()
'this sub only needs to be used once on a sheet, it adds conditional formatting that stick to the sheetSub ColorFormating()
Dim Bad As String
Dim Good As String
Dim stopiftrue As Boolean
    Bad = "13551615" 'red
    Good = "13561798" 'green
    
'*NOTE* change the column Letter to reflect the 'Status' column.  Most use ColC, but StepMaster uses colB
With Cells
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=($B1=""DELETE"")"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = Bad 'this is the "BAD" color index
            .TintAndShade = 0

        End With
        stopiftrue = False
    End With


    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=($B1=""NEW"")"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin

        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = Good 'this is the "GOOD" color index
            .TintAndShade = 0
        End With
        stopiftrue = False
    End With
End With
End Sub

 

[SURFER349]

Cool, so this adds the gridlines, but the formatting still seems to be applying to infinite columns, for 1M+ rows. How do I get this to only be on the current region?

like A1 then cntrol-All

How about

        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin

 

[Fluff]

Change this

With Cells
to
With ActiveSheet.UsedRange

 

[SURFER349]

most excellent! that works.

One last question. So I'm instructing the column to run it on with each section.
How do I get this to be referenced using a variable? Either define this variable in the top of the sub() or pass it through from a call in another sub? I'm not sure the syntax.

this is the specific section: This column can be sometimes B, sometimes C

With ActiveSheet.UsedRange    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=($C1=""DELETE"")"

maybe something like, but its not working:

sub MainThing
do stuff

call [COLOR=#333333]colorformatting("B")

[/COLOR]end sub

[CODE]sub [COLOR=#333333]colorformatting(ColStatus as String)
[/COLOR]...
With ActiveSheet.UsedRange    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=(ColStatus + "1"=""DELETE"")"
...
end sub

[/CODE]

 

[Fluff]

The formula should be

"=($" & ColStatus & "1=""DELETE"")"