Conditional formatting per row

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
60
I want to Highlight "X" cells per the value of a cell within each row, however, I need this to function on a per-row basis and to add there are up to 5 different options that the highlighted cells can be arranged.

So far I have written 2 formulas in the conditional format "=IF($H3="Square Bar",$I3)" and "=IF($H$3="Plate/Sheet",$I$3)". The way I see it is I would have to write this formula for all 5 options per row and I have 15 rows Minimum.

Is there a way to create a formula that would work for all 15 rows (per row) and work with all 5 options without having to create this over and over again?

CF1.jpg


Thanks,
Scott
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
How about like
Volume Equation.xlsx
HIJKL
1
2ShapesLengthWidthThicknessDiameter / OD
3Plate/Sheet8.5003.0000.375
4Plate/Sheet3.0605.4400.250
5Round Bar24.0002
6Square Tubing12.0002.000
7Round Tubing12.0002
8Square Bar12.0002.000
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L3:L8Expression=OR(H3="Round bar",H3="Round tubing")textNO
K3:K8Expression=H3="Plate/sheet"textNO
J3:J8Expression=OR(H3="Plate/Sheet",H3="Square tubing",H3="sqare bar")textNO
Cells with Data Validation
CellAllowCriteria
H3:H8List=Metal_Shpe
 
Solution

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
60
I need it to change as the option changes in Column "H" because there will be nothing in the cells within I3:N8 for it to use to highlight. I am wanting these cells to highlight to inform the person using the spreadsheet which cells they need to fill in with the appropriate dimensional data..
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
I am wanting these cells to highlight to inform the person using the spreadsheet which cells they need to fill in with the appropriate dimensional data..
That's what it does. ;)
 

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
60

ADVERTISEMENT

OK so I entered that info, but I am not getting the same results. In fact, nothing is highlighting... I am missing something.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Are your shapes in Col H or col I?
Assuming col H did you select L3:L last row & then use the formula I showed?
 

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
60
OK, I got it figured out.. thanks that works great!

What was happening was when I copied and pasted your formula the H3 kept changing. Sometimes it was E4 or J4, even saw H1042356. No idea why the Formula bar in Conditional formatting is so sketchy, but I got it working now... thank you so much!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,921
Messages
5,639,009
Members
417,062
Latest member
CM214

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