First let me say that I'm a novice at VBA.
I have a function called ColorCheck3 that looks at the referenced cell below it and returns a text string. I call it out by putting in cell A1 "=ColorCheck3(A2). It works fine until I start filtering (hiding) the rows below it.
I have another function called NextVis that correctly finds the next visible row below it. I call it out by putting in cell A1 "=NextVis(A2)". As I hide the rows below 1 it properly detects the next visible cell in column A.
I've been trying in vain to use NextVis to get ColorCheck3 to look at the next visible cell below it. Here's my code:
Function CheckColor3(Range)
If Range.Interior.Color = RGB(189, 215, 238) Then
CheckColor3 = "Peer"
ElseIf Range.Interior.Color = RGB(248, 203, 173) Then
CheckColor3 = "Child"
ElseIf Range.Interior.Color = RGB(198, 224, 180) Then
CheckColor3 = "Child"
Else
CheckColor3 = ""
End If
End Function
Function NextVis(Target)
If VarType(Target) = vbString Then
Set Target = Range(Target)
End If
Set rtest = Target.Offset(1, 0)
Do
If Not rtest.EntireRow.Hidden Then
NextVis = rtest.Value
Exit Do
End If
If rtest.Row = 1 Then
NextVis = rtest.Value
Exit Do
End If
Set rtest = rtest.Offset(1, 0)
Loop While NextVis = ""
End Function
Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
Is there a way to get ColorCheck3 to use NextVis as it range? Or am I just going about this in the completely wrong way?
Thanks in advance for any help.
Bookmarks