Highlight group of rows using conditional formatting

NikoleJay

New Member
Joined
May 9, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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 NumberTransaction TypeOperation DateOperation NoNSNNounQuantityCreated by
XXXXXXXXXXXXXXConsumption IssueXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX2EAXXXXXXXXXXXXXX
Remarks:

Into this:
Highlight Example.png


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:
VBA Code:
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:
Highlight Example2.png




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

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 9, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 9, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,676
Members
414,009
Latest member
SNesbyCarr

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