Conditional Formatting

dbr001

New Member
Joined
Dec 29, 2020
Messages
6
Office Version
  1. 365
Hi folks,

I am building a spreadsheet which contains an inventory list and what I need to do is have 4 query boxes on the top of the spreadsheet which you can enter total of 4 part numbers (each in each own cell) and then when you have these entered the spreadsheet will highlight the 4 matching part numbers in the spreadsheet and highlight that items entire row and will keep the rows highlighted until the query boxes part numbers are changed.

I would assume this is done by conditional formatting.

Let me know how this is doable. Greatly appreciate your help.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi dbr001,
Kindly share the same workbook having data. It would help to understand your query better and to provide solution faster.

Thanks,
Saurabh
 
Upvote 0
Yes, this would be conditional formatting based on formula. For example: =AQ10=4
With conditional formatting using this formula, the format would be applied if the value of 4 was in cell AQ10
 
Upvote 0
Hi dbr001,
Kindly share the same workbook having data. It would help to understand your query better and to provide solution faster.

Thanks,
Saurabh

Here is the spreadsheet. User inputted values go into the top right cells LF, LR, RF, RR and then that causes the query which will enable the conditonal formatting. It needs to highlight the entire row. The values being inputted are is the TIRE ID which are located in Column A. Hope this helps explain it better.
 

Attachments

  • sample spreadsheet dbr.png
    sample spreadsheet dbr.png
    179.2 KB · Views: 5
Upvote 0
Yes, use the "Formula" option of Conditional Formatting (the last option).

For your particular example, select rows 11 to the end of your data, and use this Conditional Formatting formula:
Excel Formula:
=And($A11<>"",Or($A11=$I$2,$A11=$I$4,$A11=$L$2,$A11=$L$4))
and choose your highlighting color.
 
Last edited:
Upvote 0
Got it working, missing ) at end, but now highlights row below the correct row.
Sorry, looks like the last parentheses got chopped off in my Copy/Paste.
I went back and added it back in, so if you mark that as the correct solution, it will be correct.
 
Upvote 0
Sorry, looks like the last parentheses got chopped off in my Copy/Paste.
I went back and added it back in, so if you mark that as the correct solution, it will be correct.
Joe4 - it highlights but, highlights the row below.
 
Upvote 0
Joe4 - it highlights but, highlights the row below.
That probably means that you did not start at row 11, but rather at row 10.
Whatever row you start at, you need to adjust the formula so that it applies to the very FIRST row in your selected range. It will adjust it automatically for all other rows.

So, if you started at row 10, the formula should be:
VBA Code:
=And($A10<>"",Or($A10=$I$2,$A10=$I$4,$A10=$L$2,$A10=$L$4))

Note if you look at the formula, and break it up into pieces, it should make sense what it is doing. It is actually pretty simple, just checking each possible value and making sure that the value in column A is not blank (so it doesn't highlight blank rows).
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top