Conditional Format 2 rows from value in one row

[Greg Stough]

New member on Mr. Excel, and this is my first post.

I know how to format an entire row based on the value of one cell. That's pretty straight forward, and I use that regularly. What I'd like to do is to format two (or more) rows based on the a value of a single cell. For example; When cell A1 has a value of "123" I want to apply conditional formatting to on A1:L1, BUT, I also want to format B1:L2 because value "123" is found in A1. I know how to create the formula to conditionally format the first row, but I can't figure out how to format the next row with that same formula. Of course I could create a second formula for for the second row, but I'd prefer a cleaner single solution approach. Fair enough?

Background, for those that like to read...
I'm a Business Analyst, and sometimes use Excel in some very non-traditional ways. During software testing a concatenated text file may be generated with several thousand lines (or 10's of thousands) of order information on it. The first part of each line is a "tag" that identifies the type of information found on that row (name, address, item number, PO, number, etc). But the next row may have a different tag, and entirely different information. But there will also be repeating patterns of rows too. For example there may be 6 rows (each with different types of data), and that 6-row pattern may then repeat 100's of times, but with information from entirely different orders.

The software that reads these files looks at the tags and then extracts the data for each line as needed. But I sometimes put these text files into a spreadsheet so they are easier to review and find the needed pieces of data (that's buried in all the other data). Sometimes we need to confirm that certain pieces of this data are correct during out tests. Sometimes these files are just a few lines long, and easy to review, as it, and other times they are not.

So, I'll use conditional formatting of a line to highlight the information they need to review. The business users can then quickly scan (or filter) down through 100's of lines of data and easily find the highlighted rows that they need to review. There are times that there are 2 different types of data (on different rows) that need reviewed, so I'd like search and highlight a row that has the text "PO" in it, for example, but also highlight the next row (or perhaps 2 rows down) that contains a part number, or something related. While I can create two different conditional formatting formulas, I'd like to do it all with one.

Anyway, I'm not sure if this is even possible, but if so I'd like to hear from you. Thanks!

- Greg

 

[frank_AL]

Greg,

Welcome to the Board! I can tell you from my own experience that this Forum is a great learning tool based on questions already asked/answered as well as a place to find solutions for your specific needs.

Since Conditional formatting is specific to the cell(s) it is applied to I don't know of a way to accomplish what you are requesting in native Excel. However, a solution could be created in Visual Basic (VBA) that would accomplish what you are requesting. There would be several questions that would need to be answered but I can envision a VBA solution.

Let me know if you want to go that route.

 

[Greg Stough]

Thank you Frank, for your reply and offer of assistance. I was hoping that there would be a way to the formatting to more then row with the "applies to" field. I'm hoping to keep the spreadsheet relatively simple for others to use so I'm trying to keep away from macro's and VBA. I've been studying and learning almost daily about how to use Excel, but have been focusing on non-macro and VBA solutions. At some point I will study that too. At this point I don't want any other users to be concerned about the macro advisory displayed when opening a macro enabled workbook, so I'm keeping it "basic", relatively speaking.

Thanks again Frank for your reply!

 

[dreid1011]

Thank you Frank, for your reply and offer of assistance. I was hoping that there would be a way to the formatting to more then row with the "applies to" field. I'm hoping to keep the spreadsheet relatively simple for others to use so I'm trying to keep away from macro's and VBA. I've been studying and learning almost daily about how to use Excel, but have been focusing on non-macro and VBA solutions. At some point I will study that too. At this point I don't want any other users to be concerned about the macro advisory displayed when opening a macro enabled workbook, so I'm keeping it "basic", relatively speaking.

Thanks again Frank for your reply!

Keeping it basic:

Have you tried highlighting A1:L1 then, using CTRL + to highlight B2:L2 at the same time, and then using that as your range for a single CF formula of =$A$1=123 ?

 

[pjmorris]

Hi Greg,

You can achieve your required result using a formula as the conditional formating requirement: =OR($A1="ABC",,IFERROR(OFFSET($A1,-2,0)="ABC",FALSE),IFERROR(OFFSET($A1,-1,0)="ABC",FALSE))

The IFERROR statements simply ensures that if the offset results in a negative row FALSE is returned and hence stops the conditional formating failing, but this is only really needed for the top few rows. A simpler version would be:

=OR($A1="ABC", OFFSET($A1,-2,0)="ABC",O FFSET($A1,-1,0)="ABC")

To change the number of rows highlighted you need to add or remove: the OFFSET($A1,-1,0)="ABC" elements. Adust the -1 as necessary. This would give, if you only want to highlight two rows:

=OR($A1="ABC", OFFSET($A1,-1,0)="ABC")

or for four rows:

=OR($A1="ABC", OFFSET($A1,-3,0)="ABC", OFFSET($A1,-2,0)="ABC", OFFSET($A1,-1,0)="ABC")

Hope that helps.

 

[pjmorris]

Hi Frank,

Just thought to comment that you can quite easily apply conditional formating dependent on values other than that contained in the cell itself. The 'use a formula to decide which cell to format' option provides a facility to create all manner of conditional formats and is much simpler to use than VBA. Hopefully my post back to the OP will show something of what is possible.
Regards

Peter

 

[frank_AL]

Peter,

Thank you for providing a solution as the requestor desired. One of the main reasons I participate on this board is to learn myself and this is a great example of you providing a solution I did not know existed.

Thanks!

 

[pjmorris]

Frank, I couldn't agree with you more. I learn as much as I sometimes help.

Thanks for the feedback.