Running vba on 64 bit and 32 bit

Perry72

New Member
Joined
Jun 16, 2018
Messages
5
Hi everyone,

I'm new to vba so please bear with me ?

I'm using office 2016, 64 bit on win10 and have used code from online to create a function in excel to count coloured cells. This works fine using 2016, 64bit win10 and 2010, 64 bit win7 however it does not work on 2010 32 bit win7.

Any advise ?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Perry72

New Member
Joined
Jun 16, 2018
Messages
5
For reference below is the code in question

Code:
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
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,650
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't it work?
It works fine for me on 2013 32bit
 

Perry72

New Member
Joined
Jun 16, 2018
Messages
5
Hi Fluff,

Strangely enough when I got back to work I tried to replicate the problem and it seems to have been resolved. Thanks for responding and confirming it work in 2013 32bit

Now for the next question that I hope you or the forum to help with

Can you could be modified to only count visible cells. The theory is that I would be able to filter the table and then count the colours based on the filter.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,650
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You could try
Code:
For Each rCell In rRange.SpecialCells(xlVisible)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Specialcells doesn't work properly in a UDF, so you'd need to check rCell.Entirerow.Hidden in the loop instead.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,650
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Never knew that, (y)
 

Perry72

New Member
Joined
Jun 16, 2018
Messages
5
Would I be cheeky in asking if you could modify the code I posted to ignore hidden cells

Thanks
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes. ;)

Code:
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 not rcell.entirerow.hidden then
If rCell.Interior.ColorIndex = lCol Then

vResult = WorksheetFunction.SUM(rCell, vResult)

End If
end if
Next rCell

Else

For Each rCell In rRange
If not rcell.entirerow.hidden then
If rCell.Interior.ColorIndex = lCol Then

vResult = 1 + vResult

End If
End if
Next rCell

End If

ColorFunction = vResult

End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,238
Members
409,857
Latest member
KailuaTown
Top