Highlight group of rows using conditional formatting

[NikoleJay]

Hello!

I am working on a product that will help track when documents were filed. The idea is that when I type "Filed" in the cell next to "Remarks," then it will highlight that entire row, plus the row above and below it. After labeling everything, I'm going to set up a VBA to move the highlighted rows to another sheet.

So, I would like the conditional formatting to turn this:

Doc Number	Transaction Type	Operation Date	Operation No	NSN	Noun	Quantity	Created by
XXXXXXXXXXXXXX	Consumption Issue	XXXXXXXXXXXXXX	XXXXXXXXXXXXXX	XXXXXXXXXXXXXX	XXXXXXXXXXXXXX	2EA	XXXXXXXXXXXXXX
Remarks:

Into this:

Currently, I have a VBA to set up the conditional formatting, but I can't figure out how to get it to highlight multiple rows. This is the VBA:

Sub ConFormat()
'
' Add Conditional Format
'
lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastCol = ActiveSheet.Range("A:H").Column
    Sheets("Document Status").Range("A4", Sheets("Document Status").Cells(lastRow, lastCol)).Select

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B4=$B$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -11489280
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False

End Sub

Which makes it look like:

Is it possible to make it highlight all three rows? Is there a better way to go about this? Any help would be appreciated!

 

[jasonb75]

I think that you will just need to change your condition to =AND($B3:$B5=$B$2)
Although applying conditional formatting to move the rows should not be necessary unless you want to do a visual check first.

Just find "Filed", then set your range to move based on an offset of -1 row, resized to 3 rows.

 

[NikoleJay]

I think that you will just need to change your condition to =AND($B3:$B5=$B$2)
Although applying conditional formatting to move the rows should not be necessary unless you want to do a visual check first.

Just find "Filed", then set your range to move based on an offset of -1 row, resized to 3 rows.

Using =AND($B3:$B5=$B$2) didn't work, but using find and offset did. I did want the visual check first, but I think I'm going to settle for it just highlighting one row for that.
Thank you for the suggestion!

 

[jasonb75]

Sorry, I was thinking one thing and typed another, it should have been OR not AND, although this was based on what I could see, as the worksheet addresses are not visible in your post I had made assumptions.

Normally, you would make 3 separate rules, 1 to check if the current row meets the criteria, another for the row above and a third for the row below. However, as you have code to identify the rows to highlight, you could simply use that code to apply formatting directly to the cells in question without using conditional formatting.

 

[NikoleJay]

Using "OR" worked!! Sorry for the late reply. I took a long weekend and didn't have my work laptop with me. I'm going to stick with the code to apply formatting, but this is still useful if I need it for another project. Thank you for your help!