Formatting of entire column based on the grand total using conditional formattin

Question

Formatting of entire column based on the grand total using conditional formatting color scale?

This might be answered somewhere else, but I haven't been able to find a solution to this. The question is how to use color scale for entire column just based on grand total.

I have attached the picture for reference./

I am happy with the color scale on the grand total and want the entire column to take up that color but if I copy the format it turns the entire color scale goes haywire. e.g. I want entire column ab to become green instead of having yellow in 4 rows and green in 3rd as well as all the grand total turned green.

Explanation / Answer

..For this to work, first create a named range - say, tblData - that refers to the top left cell of your table, so that the macro can select the table - this also depends on the table being entirely separated from other data by completely blank columns and rows, so you may need to insert some buffer space around your table.^*^

Second, change your macro to use the named range's currentregion:

Sub ColorTableBasedOnBottomRowCFScale()
    Dim rngS As Range
    Dim rngD As Range
    Dim i As Integer

    Set rngD = Range("tblData").CurrentRegion

    Set rngS = rngD.Rows(rngD.Rows.Count)

    For i = 1 To rngS.Cells.Count
        rngD.Columns(i).Cells.Resize(rngD.Rows.Count - 1).Interior.Color = rngS.Cells(1, i).DisplayFormat.Interior.Color
    Next i
End Sub

And, finally, copy this event code, right-click the sheet tab, select "View Code" and paste the code into the window that appears.

Private Sub Worksheet_Calculate()
    ColorTableBasedOnBottomRowCFScale
End Sub

Then you're done, and any changes to the data will result in updated colors. As far as users who don't enable macros - well, you could hide all your sheets except one that has, in large bold letters

YOU MUST ENABLE MACROS TO USE THIS WORKBOOK

and then use the workbook open event to hide that sheet and unhide the others, and the before close event to re-hide and re-save....    or just bribe your users with a large donut ;-)

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.