Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

dgillz

Instructor
Mar 2, 2001
10,043
US
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:
 
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
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:
 
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
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:
 
Plz upload your workbook. Need to know how this sheet is structured.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Here it is.

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
 
 http://files.engineering.com/getfile.aspx?folder=f2a1961f-cd8c-41a5-9367-73351cbcdd54&file=Minimum_with_Conditional_coloring.xlsm
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 [b]Intersect(Columns(6), ActiveSheet.UsedRange)[/b]
        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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
 http://files.engineering.com/getfile.aspx?folder=25ddbb18-a210-4f08-b30a-1885e5691bc0&file=Minimum_with_Conditional_coloring.xlsm
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:
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top