INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Cell Background color from min() of a range of different colored cells

Cell Background color from min() of a range of different colored cells

(OP)
I have 5 columns of numbers, each with a different cell background color. In the 6th column, I use the MIN() function to get the smallest of the 5 cells in that row. This is as easy as can be. My dilemma - I need the background color of the cell with the MIN() function to be the same background color as the cell that actually contains the minimum value for that row.

Any idea how I would approach this in VBA or otherwise?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Cell Background color from min() of a range of different colored cells

Hi,

YOU must FIRST select the range to shade with colors...

CODE

Sub MatchColors()
'select the MIN range to color
    Dim r As Range, c As Range
    
    For Each r In Selection
        For Each c In Range(r.Offset(0, -5), r.Offset(0, -1))
            If r.Value = c.Value Then
                r.Interior.Color = c.Interior.Color
                Exit For
            End If
        Next
    Next
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Cell Background color from min() of a range of different colored cells

(OP)
I'm confused. The range is already colored. I need the cell with the MIN() formula (column F) to take on the same cell color as the actual minimum value of column A thru E for that row. So in row 1 that could be green, in row 2, grey, in row 3 red, etc.

And where/how do I tell Excel which 5 columns to look at?

How would this code ever execute?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Cell Background color from min() of a range of different colored cells

That's exactly what happens.

My code ASSUMES that your 5 columns are followed by a 6th column with the MIN formula any that YOU will select the data in that column, since you privided no other information.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Cell Background color from min() of a range of different colored cells

(OP)
This works, but I have manually invoke the module, and it only works for the first row. Should it not be in a module? How do get the colors to change if some manually changes the values in columns A thru E? How do I get it t work for all the rows that are populated and shaded?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Cell Background color from min() of a range of different colored cells

Plz upload your workbook. Need to know how this sheet is structured.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Cell Background color from min() of a range of different colored cells

Here's your workbook with code modifications.

1) the procedure that changes the colors in a standard module...

CODE

Sub MatchColors()
'SkipVought 2017 AUG 16
    Dim r As Range, c As Range
    
    For Each r In Intersect(Columns(6), ActiveSheet.UsedRange)
        For Each c In Range(r.Offset(0, -5), r.Offset(0, -1))
            If r.Value = c.Value Then
                r.Interior.Color = c.Interior.Color
                Exit For
            End If
        Next
    Next
End Sub 

The procedure that captures the change event on the sheet in the worksheet code sheet...

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Parent.UsedRange) Is Nothing Then
       MatchColors
    End If
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Cell Background color from min() of a range of different colored cells

(OP)
Excellent stuff, thank you very much SkipVought.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close