Help with Conditional Formatting Please

[Paul Black]

Hi,

I have Two Columns C & D with Data.
The Cells with Data in Column D are Highlighted for MIN in Red and MAX
in Blue.
I would like the Cell in Column C that is to the Left of the Highlighted
MIN Value in Column D to ALSO be Coloured Red, and the Cell in Column C
that is to the Left of the Highlighted MAX Value in Column D to be
Coloured Blue.

Any Help will be Greatly Appreciated.

All the Best.
Paul

 

[Guest]

Hi,

this will highlight required cells. Change rng1 to suit your data

Dim rng1 As Range, minx As Long, maxx As Long

Set rng1 = Range("D1" & Range("D" & Rows.Count).End(xlUp))

minx = rng1.Cells(Application.Match(Application.Min(rng1), rng1, 0)).Row
Cells(minx, "D").Interior.ColorIndex = 3
Cells(minx, "C").Interior.ColorIndex = 3


maxx = rng1.Cells(Application.Match(Application.Max(rng1), rng1, 0)).Row
Cells(maxx, "D").Interior.ColorIndex = 41
Cells(maxx, "C").Interior.ColorIndex = 41

HTH

 

[Tom Ogilvy]

Put in the same formulas in column C as you have in column D - make sure
they refer to column D and not column C.

 

[Bob Phillips]

Paul,

Select column C & D, and in the CF formua, use

=$C1=MAX($C:$C)

and

=$C1=MIN($C:$C)

note the $ signs

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Paul Black]

Thanks Tom & Bob for the Replies.

I have Tried Both Suggestions But Unfortunately they do Not give me the
Correct Results.
The Formulas I am Using in Conditional Format for Column D are :-

Cell Value is Equal to =Min($D$4:$D$305)
Cell Value is Equal to =Max($D$4:$D$305)

The Values in Column C are NOT Sequential so I think that this might be
Part of the Problem.

Thanks in Advance.
All the Best.
Paul

 

[Tom Ogilvy]

Bob has given you the correct formulas. Select columns C and D as
instructed. (C1 or D1 should be the activeCell) Then When you go to
format=>Conditional formatting, change the first dropdown from Cell Value is
to Formula is, then enter Bob's first formula. Set the formatting, then add
a formula and repeat

Order has no affect on whether the value of a cell matches a test value.

 

[Tom Ogilvy]

Actually, Bob has given you formulas for the min and max values in column C.
For D as you described, change them to:

=$D1=MAX($D:$D)

and

=$D1=MIN($D:$D)

 

[Paul Black]

Thanks Toppers, Tom and Bob,

What I Forgot to Include was the Fact that the Values Started in Cell C4
and Finished in Cell D305.

I Used the Formula :-

Formula is =$D4=MIN($D$4:$D$305)
and
Formula is =$D4=MAX($D$4:$D$305)

Both Work Great Thanks.

All the Best.
Paul