INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

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

 Forum Search FAQs Links MVPs

## 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:

### 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,

Just traded in my OLD subtlety...
for a NUance!

### 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:

### 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,

Just traded in my OLD subtlety...
for a NUance!

### 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:

### 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,

Just traded in my OLD subtlety...
for a NUance!

### 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,

Just traded in my OLD subtlety...
for a NUance!

### 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:

#### 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.

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

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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!