Conditional formatting for cells depending on other cells ranges [Solved/Closed]

Report
Posts
8
Registration date
Thursday April 10, 2014
Status
Member
Last seen
July 1, 2014
-
Posts
8
Registration date
Thursday April 10, 2014
Status
Member
Last seen
July 1, 2014
-
First off, I am not even sure if i worded my question correctly, so sorry if i have.
What i want to do is... have for cells b4:b7 to have on green cell for the highest value from h4:h7. I can do it singularly, but id like to know if there is some function im missing because this could buy me a lot of time.

For example is there a function to apply whatever cell is selected when copying conditional formatting? Or something along the lines of

If "greatest" h4:h7 then "format green"

Or can i do a condition from a condition? How would i put that in a problem?

Again I'm sorry if this is confusing, and im sure i can better word this, im just slightly frustrated.

3 replies

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Derrall,

Or use this formula in conditional formatting in cell B4:
=MAX($H$4:$H$7)=H4

Now drag the formula down applying format only.

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
I am rephrasing sit see whether it tells what you want

i have some entries (numbers) in B4 to BV7
I have some number H4 to H7 also

if any cells in B4 to B7 has got the maximum of H4:H7 it shd be colored green

if so try this macro

Sub test()
Dim r As Range, c As Range, mmax As Double
Set r = Range("B4:B7")
mmax = WorksheetFunction.Max(Range("H4:H7"))
r.Cells.Interior.ColorIndex = xlNone
For Each c In r
If c = mmax Then c.Interior.ColorIndex = 4
Next c
End Sub
Posts
8
Registration date
Thursday April 10, 2014
Status
Member
Last seen
July 1, 2014

This is 100% what i needed thank you TrowaD ^_^

And venkat1926 thank you for your assistance too!

Again sorry if my question was asked incorrectly, in the end i got exactly what i needed!