Conditional Formatting - Highlight

[echodeltaink]

Looking for help to highlighting cells automatically.

If any cells from A5:A7 is equal to One, Then highlight the lowest value from A1:A3 (3 would be highlighted)
If any cells from B5:B7 is equal to One, Then highlight the lowest value from B1:B3 (2 would be highlighted)

***A***B
1 **5** 6
2 **3** 2
3 **5** 5
4 ******
5 **1** 1
6 **2** 1
7 **2** 2

 

[T. Valko]

Looking for help to highlighting cells automatically.

If any cells from A5:A7 is equal to One, Then highlight the lowest value from A1:A3 (3 would be highlighted)
If any cells from B5:B7 is equal to One, Then highlight the lowest value from B1:B3 (2 would be highlighted)

***A***B
1 **5** 6
2 **3** 2
3 **5** 5
4 ******
5 **1** 1
6 **2** 1
7 **2** 2

What version of Excel are you using?

 

[Marcelo Branco]

Hi,

Maybe this

Select A1:A3
Conditional Formatting
New Rule
Use a formula to...
=AND(COUNTIF($A$5:$A$7,1)>0,MIN($A$1:$A$3)=A1)
pick a format you want

Do the same for B1:B3

HTH

M.

 

[Marcelo Branco]

Easier, you can select A1:B3 and

Conditional Formatting
New Rule
Use a formula to...
=AND(COUNTIF(A$5:A$7,1)>0,MIN(A$1:A$3)=A1)
pick a format you want

HTH

M.

 

[echodeltaink]

Hello, I am using 2007 Thanks

 

[echodeltaink]

Hello Marcelo,
I tried using your codes and no format changes occured.
Thanks,

 

[T. Valko]

Hello, I am using 2007 Thanks

Ok, try this...

Select the *entire* range A1:B3 starting from cell A1. Cell A1 will be the
active cell. The active cell is the one cell in the selected range that is not shaded. The formula will be relative to the active cell.

• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
• =AND(COUNTIF(A$5:A$7,1),A1<>"",A1=MIN(A$1:A$3))
• Click the Format button
• Select the desired style(s)
• OK out

 

[Marcelo Branco]

Hello Marcelo,
I tried using your codes and no format changes occured.
Thanks,

It worked for me

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: yellow; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR></TBODY></TABLE>

=AND(COUNTIF(A$5:A$7,1)>0,MIN(A$1:A$3)=A1)

M.