Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Help!! SumIf conditional Formatted Color

0

Hi All!

I've a problem about how to SumIF color from conditional Formatting. I've googled some answers and write the vba, but the result formula is #VALUE even though form the formula bar (function argument) the result are fine. This the vba code:

Function SumColor(MatchColor As Range, sumRange As Range) As Double
  Dim cell As Range
  Dim myColor As Long
  myColor = MatchColor.Cells(1, 1).DisplayFormat.Interior.Color

  For Each cell In sumRange
    If cell.DisplayFormat.Interior.Color = myColor Then
        SumColor = SumColor + cell.Value
    End If
  Next cell
End Function

i also uploaded the file

it really helps me a lot, if i can fix this problem. 

excel
posted by: antomon (rep: 2) on Fri May 21, 2021 at 5:16 am
Answer
Discuss

Answers

0
Selected Answer

My research led me to conclude that you have discovered a bug. VBA fails to read the DisplayFormat in a UDF. It's a pity, since you invested so much time and effort into your concept.

In the attached workbook the work flow is to enter something on the MEI tab. If the entry qualifies the cell colour changes. There are many of your excisting entries. Just select any of them and press F2 (to enter Edit mode) and Enter. This action also changes the value of the related cell on the Uang tab. The formula existing there will be over-written. The code also changes the colour of that cell. Please remove the CF because your CF over-rides the colouring set by the code.

When you activate the Uang tab all totals in columns AU:AW are calculated. That means that those totals may not be up to-date when changes are made in MEI. If you look at Uang, go back to MEI and make a change, Uang totals will not reflect the change until you look at it again.

All colouring is done to match Uang!AU:AW, both for fill and font. The font colour is not considered in the SumByColor function but you don't see many totals there because most of the colouring in Uang is done by CF.

Look for the code in module TXL_5438 and in the code modules of MEI and Uang. I tried to keep the code simple and transparent but if you needf any explanation please don't hesitate to ask.

posted by: Variatus (rep: 4889) on Fri May 21, 2021 at 9:50 pm
Discuss

Discussion

Thank you so much for your response, the problem is i'm not good enough with VBA :(.  Could you give me an example on how to change the cells color without using Conditional Formatting, i've googled some example but it didn't work out with my current problem.
antomon (rep: 2) May 25, '21 at 12:55 pm
wow thanks a lot, color changed works perfectly... i did some tweaks on cellvalue function and the numbers works too. The value in MEI are actually the codes for bussiness trips expense, it goes like this

D/M/R = the division 
1-3 number = transportation expenses
4-6 number = daily expenses
7-9 number = accomodation expenses
A/F = to distinguish if the expense already covered by company or not

For the division i want to give it color fill, and for the A/F initially distinguished by color red and black.

By your helps, total expenses per division already solved. I'm still trying work the vba you provided to give fontcolor by A/F in sheets MEI and sum it in (AX5:AY5)..

Anyway, Thank You So Much.
antomon (rep: 2) May 28, '21 at 5:10 am
Hi Antomon,
Thanks for the feedback and for selecting my answer. If you have another question regarding the same project later on please don't hesitate to ask another question based on what the project looks like after your study and further input. I'm sure it will turn out very nicely.
Variatus (rep: 4889) May 28, '21 at 7:28 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login