User Profile
Hr_Justino
Copper Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: Count and Sum by Background Color, with color set by both Manual and conditional formatting
I see I misunderstood how to input the code. I seperated it into different modules. (Module 1,2,3 for each section of code) I guess that is why it did not work first time around. I have input Your code in a single module and it just works now, with decimals. Thank you HansVogelaar6.4KViews0likes0CommentsRe: Count and Sum by Background Color, with color set by both Manual and conditional formatting
Hi HansVogelaar I have fixed the issue With ColourSum not working and in turn have made it so DColorindex would not be needed. Here is the Code that does all I require (I Changed Double to Long in the Function ColourSum ) ________________________________________________________________________________________ Function ColourCount(cel As Range, ran As Range) As Long Dim colo As Long Dim c As Range Dim cou As Long colo = cel.Interior.ColorIndex For Each c In ran If ran.Parent.Evaluate("DColorIndex(" & c.Address & ")") = colo Then cou = cou + 1 End If Next c ColourCount = cou End Function ________________________________________________________________________________________________ Function ColourSum(cel As Range, ran As Range) As Long Dim colo As Long Dim c As Range Dim colsum As Long colo = cel.Interior.ColorIndex For Each c In ran If ran.Parent.Evaluate("DColorIndex(" & c.Address & ")") = colo Then colsum = colsum + c.Value End If Next c ColourSum = colsum End Function _________________________________________________________________________________________ Many Thanks HansVogelaar6.4KViews0likes2CommentsRe: Count and Sum by Background Color, with color set by both Manual and conditional formatting
Hi HansVogelaar I have Input the code, and removed my old code to a text file on my desktop. I run into issues when I try to run DColorIndex. I dont understand how to make the function work. Sorry this might be a bit basic. Your ColourCount works lovely. But I also had trouble wit ColourSum. The debugger show up on the line "For Each c in ran" If you could explain how to use the function DColorIndex that would be great. I assumed I would write the function and choose the control color and then hit enter. "doesn't work"6.4KViews0likes0CommentsCount and Sum by Background Color, with color set by both Manual and conditional formatting
Hello, I have made an add-in to count and sum when colors are manually set. However when Conditional Formatting is used I.E. Setting Equal to 1 to have a red background. The VBA code ignores the Conditional format and instead counts or sums as if the color didn't change. Is there some VBA code that can count/Sum by background color which also includes conditional formatting changes. (The conditional Format is set to Equal to 1 background is red) As in picture the red field are counted when in reality. I dont think they should since their color is red and not yellow or orange. Hope this makes sense to someone.Solved6.8KViews0likes10Comments
Recent Blog Articles
No content to show