color coding in VBA

[louismk]

Is there someone who can help me color code part of this vba macro? What this macro does is take a range of numbers and tells the order and amount of zeros vs. non-zeros. So for example if there was a range of 1-6-0-4-0-0-7-4 this macro would return in one cell "2-1-1-2-2"- the first 2 would represent the 1 and the 6 from the original range, the next 1 would represent the first 0 from the original... What I want to do is color code part of the macro so that any return numbers that represent the number of zeros would be red. So in the example of "2-1-1-2-2" the first 1 and the first 2 would be red since they represent zeros from the original range. I tried to do it in vba by inserting [color 3] under the part that controls the numbers that return the zero representations but it either does not run when I do that or it will not color code. The vba code that runs this formula is listed below, anyone out there know of a trick to solve this? Thanks in advance!

 

[louismk]

I guess it'd help if I sent the actual vba code, here it is:

[pre]

Function NumbZeros(myData) As String

Dim myArr
Dim myStr As String

On Error Resume Next

Count = 0
Count0 = 0
myStr = ""

Set myArr = myData

For i = 1 To myData.Count - 1

If myArr(i) <> 0 Then 'Data <>0
Count = Count + 1
If i + 1 = myData.Count Then
If myArr(i + 1) = 0 Then
myStr = myStr + Str(Count) + "-1"
Else
myStr = myStr + Str(Count + 1)
End If
ElseIf myArr(i + 1) = 0 Then
myStr = myStr + Str(Count) + "-"
Count = 0
End If
Else ' Data =0
Count0 = Count0 + 1
If i + 1 = myData.Count Then
If myArr(i + 1) = 0 Then
myStr = myStr + Str(Count0 + 1)
Else
myStr = myStr + Str(Count0) + "-1"
End If
ElseIf myArr(i + 1) <> 0 Then
myStr = myStr + Str(Count0) + "-"
Count0 = 0
End If
End If
Next

If Right(myStr, 1) = "-" Then myStr = Left(myStr, Len(myStr) - 1)

NumbZeros = myStr

End Function

[/pre]

 

[Hui]

Louismk


Firstly my apologies as I should have added this commentary to my answer to your original post.


One of the limitations of using User Defined Functions (UDF's) is that you can't programatically change the cells contents.

The UDF doesn't return a Text String, it just shows the answer which you want in the same way a =Sum(A1:A5) shows an answer but the cell still contains the function =Sum...


Although writing the code to color the characters for a text string in this format is very easy, because the Text String doesn't exist you just can't call another subroutine to do the coloring.


Also in VBA as fas as I know, and I'd love to be proven wrong, it is not possible to build up a string with characters including the characters properties.


So where does that leave us:

Would you be happy for the cell to be colored Red or Green if the First or Last character is 1/0, or do you have any other ideas for display.

 

[louismk]

Hui,

No problem with the original post, your macro brought me very far in my project. That is not a bad idea aout the cell outlines but, and correct me if I'm wrong, I can just do that with a simple conditional format right? Also- I read your post about donught charts being invented by American cops- halarious.

 

[Hui]

You can use conditional formatting and you can base the Cells Color on either

the first cell of the 13 Cell Range, or

The first character of your cell


I thought I may have got more feedback from the American Cops post, but obviously they don't read Chandoo.org, at least they can't arrest me in Australia.