PDA

View Full Version : Cells Background Colour Match Help :bow:



thunderzhao
11-13-2011, 05:54 AM
Hi guys, i'm trying to write a function that returns an array of 1's and 0's to indicate match or not match of a range.


Function Countcolour(rng As Range, colour As Range) As Integer
Dim c As Range
Dim i As Integer
Dim ColorMatch() As Integer
i = 0
Application.Volatile

For Each c In rng
ColorMatch(i) = 0
If c.Interior.ColorIndex = colour.Interior.ColorIndex Then
ColorMatch(i) = 1
End If
Next
End Function


Can someone fix it for me.
Thanks a million

mdmackillop
11-13-2011, 06:10 AM
Welcome to VBAX

Option Explicit
Sub Test()
Dim r As Range, c As Range
Set r = Range("F1:F20")
Set c = Range("D1")
r.Offset(, 3).Value = Application.Transpose(Countcolour(r, c))
End Sub
Function Countcolour(rng As Range, colour As Range)

Dim c As Range
Dim i As Long
Dim ColorMatch() As Long

Application.Volatile
ReDim ColorMatch(rng.Count - 1)
For Each c In rng
ColorMatch(i) = -(c.Interior.ColorIndex = colour.Interior.ColorIndex)
i = i + 1
Next
Countcolour = ColorMatch
End Function

thunderzhao
11-13-2011, 06:15 AM
Thank you soooo much for your fast response. Now i'm trying to understand this