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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi dbr001,
Kindly share the same workbook having data. It would help to understand your query better and to provide solution faster.

Thanks,
Saurabh
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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
 

dbr001

New Member
Joined
Dec 29, 2020
Messages
6
Office Version
  1. 365
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: 4

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

dbr001

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

ADVERTISEMENT

Thanks.. Plugged it in, but not working.
Got it working, missing ) at end, but now highlights row below the correct row.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
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.
 

dbr001

New Member
Joined
Dec 29, 2020
Messages
6
Office Version
  1. 365
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,736
Messages
5,626,592
Members
416,194
Latest member
Dhisilva_aguiar

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