add sum colour cell to a button

Kubi · #1 05-17-2017, 12:30 PM

I can add a sum colour cell formula and i can add a button however i can not get them to work together.

or alternately can you enter a formula to colour a different cell based on whats entered across other cells.
for example (if sheet1A2 = data!A1:A10 then colour F2 red) is something like that possible??

Logit · #2 05-17-2017, 01:21 PM

Need more info.

Kubi · #3 05-17-2017, 01:54 PM

I would like to have a button on the sheet assigned to sum a cell colour so someone can go through and colour the cells (lets say within rows B&C) and then hit the button to provide the total sum.

different button for each colour.

does that help?

Logit · #4 05-17-2017, 02:21 PM

Paste this code in the Sheet Level Module:

Code:

Option Explicit

Sub cmdBtn1()
   Sheets("Sheet1").Range("A3").Value = "=ColorFunction(A3,B2:C100,TRUE)"
End Sub

Sub cmdBtn2()
   Sheets("Sheet1").Range("A7").Value = "=ColorFunction(A7,B2:C100,TRUE)"
End Sub

Paste this code into a Routine Module:

Code:

Option Explicit

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
    If SUM = True Then
        For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = WorksheetFunction.SUM(rCell, vResult)
                End If
        Next rCell
            Else
                For Each rCell In rRange
                    If rCell.Interior.ColorIndex = lCol Then
                        vResult = 1 + vResult
                    End If
                Next rCell
    End If
ColorFunction = vResult
End Function

You can expand the colors and buttons by following the pattern in the code.

Kubi · #5 05-17-2017, 02:37 PM

thank you for that, i will give it a try and let you know.

Logit · #6 05-17-2017, 03:45 PM

You are welcome.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Colour code mail merge header table cell backgrounds	ScotsMaverick	Mail Merge	25	11-04-2021 02:07 PM
colour codeing a cell by entering data	gazza uk	Excel	6	05-29-2014 09:52 AM
VBA to immediately change the colour of a cell depending on the code placed in anothe	Phil Payne	Excel Programming	2	07-27-2013 11:04 PM
Quadrant colour based on cell value	RoyLittle0	Excel	2	05-05-2013 12:50 AM
CHange colour of footer if a cell changes to red	OTPM	Excel	0	05-26-2011 07:15 AM