Change Color in cell by lowest dollar value on row

craigwojo

Board Regular
Joined
Jan 7, 2005
Messages
226
I'm making a chart to see which vendor has the lowest price for the same product. I have 4 values (F4, I4, L4 and O4). I would like to have the lowest value of the cell use the "data validation" option and make the cell a light green (or whatever color I choose).
How can I do this?

Thank you and have a Merry CHRISTmas.
craigwojo
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,088
Office Version
  1. 365
Platform
  1. MacOS
Using Conditional formattin
for 2007, 2010 , 2013 or 2016 excel version
Conditional Formatting

Highlight applicable range >>

F4, I4, L4 and O4



Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=F4=MIN($F4, $I4, $L4 , $O4)


FormatÖ [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Not sure I understand the DataValidation part

Book1
FGHIJKLMNO
3
46543
5
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4,I4,L4,O4Expression=F4=MIN($F4,$I4,$L4,$O4)textNO
 

craigwojo

Board Regular
Joined
Jan 7, 2005
Messages
226
Thank you so much.
If I have many rows to do, would I need to do each row independently or is there a way to make it work on each row I select? Note: all cells that I use will be in the same column.

Thank you and God bless.
Craig
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,088
Office Version
  1. 365
Platform
  1. MacOS
No,
it was only because you have non-consecutive range
Book1
ABC
1Formula example
211
32
43
54
65
71
82
93
104
115
126
131
142
153
164
175
186
19
Sheet1
Cell Formulas
RangeFormula
C2C2=MIN(B:B)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B:BExpression=B1=MIN(B:B)textNO
 

craigwojo

Board Regular
Joined
Jan 7, 2005
Messages
226
Thank you etaf.
I'm a little confused but maybe I didn't explain myself completely.

I'm making a chart to see which vendor has the lowest price for the same product. I have 4 values (F4:F99, I4:I99, L4:L99, and O4:99) IN EACH ROW. Also, I have a total of 99 rows total (and probably would be adding more) I would like to have the lowest value of the cell IN EACH ROW use the "data validation" option and make the cell a on each row light green (or whatever color I choose).
How can I do this?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,088
Office Version
  1. 365
Platform
  1. MacOS
I would like to have the lowest value of the cell IN EACH ROW use the "data validation" option and make the cell a on each row light green (or whatever color I choose).
Data Validation, can you explain how that would work, or do you mean conditional formatting
select the NON Contiguous Range $F$4:$F$15,$I$4:$I$15,$L$4:$L$15,$O$4:$O$15
then using a formula rule
=F4=MIN($F4,$I4,$L4,$O4)

Book1
ABCDEFGHIJKLMNO
1ProductVendor1Vendor2Vendor3Vendor4
2
3
4Product11234
5Product24578
6Product32137
7Product48735
8Product59737
9Product6108227
10Product712927
11Product81410447
12Product9161157
13Product101812778
14Product1121348
15Product122168
16
17 I have 4 values (F4:F99, I4:I99, L4:L99, and O4:99) IN EACH ROW. Also, I have a total of 99 rows total (and probably would be adding more) I would like to have the lowest value of the cell IN EACH ROW
18
19
20
21
22
23
24
25
26
27
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4:F15,I4:I15,L4:L15,O4:O15Expression=F4=MIN($F4,$I4,$L4,$O4)textNO
 

Forum statistics

Threads
1,147,474
Messages
5,741,341
Members
423,655
Latest member
pepo1985

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