Refreshing a Worksheet to Calculate a Formula

[JRega]

Hello,

I have a function to highlight cells in the spreadsheet that have a specific value and then count the total number for cell that are highlighted by using the formula: =CountColorIf(cell number with the color to match, range of cells to check).

When I run the function, the cells highlight correctly but the total number of highlighted cells does not display. If will only display if I copy and paste the CountColor formula into it's current cell. The copy\paste seems to "refresh" the cell and then it will display the formula. I have tried using Worksheet(worksheet name).Calculate but this does not seem to work. Any suggestions?

Thanks,
Jenifer

 

[DILIPandey]

Hi Jenifer,

May be, after pasting in current cell, it gets the color code in the formula syntax.
Can you share the code of custom formula here ?

Regards,
DILIPandey

 

[JRega]

Here is the code I am using:

=CountColorIf($P$1,I3:N3)

Cell P1 contains the background color to match. Cells "I3:N3" refer to the cells I want checked to see if they have the color.

If I change the color of one the cells in the range I3:N3 (from not highlighted to highlighted), the formula does not update to reflect the count correctly. It will do this only if I copy/paste the formula into the same cell. This seems to refresh the formula and it displays the correct number.

 

[DILIPandey]

Hi JRega, I was expecting the code for the function CountColorIf. Can you share that ? thanks.


Regards,
DILIPandey

 

[JRega]

Hi -

The CountColorIf is a built-in Excel formula, so I did not write the code for that.

 

[63falcondude]

Hi -

The CountColorIf is a built-in Excel formula, so I did not write the code for that.

"CountColorIF" is not a built-in Excel formula. Rather, it is a UDF (User Defined Function) which has code behind it.

 

[DILIPandey]

HI 63falcondude, Thanks for clarifying it to JRega


Regards,
DILIPandey

 

[JRega]

Sorry about that. I thought it was built in.

I just did a Google and found the code:

Function CountColorIf(rSample As Range, rArea As Range) As Long
Dim rAreaCell As Range
Dim lMatchColor As Long
Dim lCounter As Long

lMatchColor = rSample.Interior.Color
For Each rAreaCell In rArea
If rAreaCell.Interior.Color = lMatchColor Then
lCounter = lCounter + 1
End If
Next rAreaCell
CountColorIf = lCounter
End Function

 

[DILIPandey]

Hi JRega,

Not sure if that code is in use or some other code in the function CountColorIf

Press Alt + F11 to come to VB Editor window and there you can find this code in your workbook / worksheets.


Regards,
DILIPandey