Conditional formatting for cells depending on other cells ranges

Solved/Closed
Derrall Posts 8 Registration date Thursday April 10, 2014 Status Member Last seen July 1, 2014 - Apr 10, 2014 at 02:31 PM
Derrall Posts 8 Registration date Thursday April 10, 2014 Status Member Last seen July 1, 2014 - Apr 16, 2014 at 03:03 PM
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

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Apr 15, 2014 at 11:31 AM
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
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 11, 2014 at 01:39 AM
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
0
Derrall Posts 8 Registration date Thursday April 10, 2014 Status Member Last seen July 1, 2014
Apr 16, 2014 at 03:03 PM
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!
0