VBA XL97 Visible Cells Only 3

[vaneagle]

Hi,

I am new to VBA and stumbling along, came across my latest issue. I have the following code:

Sub find()

Dim nCol As String
Dim nRow As String

Range("a1").Select
Const Light_Yellow = 36

With Sheets("Sheet1")
    For Each c In .Range("a1:a10")
        If c.Interior.ColorIndex = Light_Yellow Then
        nCol = c.Column
        nRow = c.Row
        
        Result = msgbox("The cell is at col " & nCol & " and row " & nRow, , "It Works!!")
       
        Else
        End If
    Next
End With

End Sub

It works fine except when I come to hidden rows. It returns the location of the hidden row as well subject to the criteria above... How can I change my code so that it looks at visible cells only??

 

[tony813]

Hello vaneagle

Maybe by changing the following on your code:

If c.Interior.ColorIndex = Light_Yellow Then
nCol = c.Column
nRow = c.Row

Result = msgbox("The cell is at col " & nCol & " and row " & nRow, , "It Works!!&quot

Else
End If

To the following:

If c.Interior.ColorIndex = Light_Yellow Then

nCol = c.Column
nRow = c.Row

r = r + 1
If Not (Rows(r).Hidden) Then
MsgBox "The cell is at col " & nCol & " and row " & nRow, , "It Works!!"
End If
Else
End If

tony_813

Tony813

 

[vaneagle]

Hi Tony813

Thanks for the suggestion. I tried it but it still came up with the hidden row.

Any other suggestions ??

Regards,
vaneagle

 

[Loomah]

Hi
Try changing

For Each c In .Range("a1:a10")

to

For Each c In Range("A1:A10").SpecialCells(xlCellTypeVisible)

in yor original code.

Enjoy
Happy Friday!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[vaneagle]

Thanks for that Loomah it worked a treat... except that I can't seem to get the code to work when there is conditional formatting....

Here is the code I have:

Sub find()

Dim nCol As String
Dim nRow As String
Dim count As Integer
Dim CountText As String

'Range("a1").Select
Const Light_Yellow = 19
count = 1
 
 
With Sheets("Sheet1")

    Select Case count
    Case 1
    CountText = "st"
    Case 2
    CountText = "nd"
    Case 3
    CountText = "rd"
    Case 4
    CountText = "th"
End Select
    For Each c In Range("A1:A10").SpecialCells(xlCellTypeVisible)
        If c.Interior.ColorIndex = Light_Yellow Then
        nCol = c.Column
        nRow = c.Row
       
        Result = msgbox("The " & count & CountText & " cell is located at col " & nCol & " and row " & nRow, , "It Works!!")
        count = count + 1
        Else
        
        End If
    Next
End With

End Sub

Is there a way around the conditional formatting ?

 

[xlbo]

Aha - conditional formatting colours are not checkable via VBA - they do not return anything to the interior.colour object - you would need to perform the same check (via code) that the conditional formatting does and use the result of that to determine if the cell will have a colour set by conditional formatting

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[vaneagle]

Doh!

oh well.... Thanks for the info and reply Geoff... I will revisit my approach to the issue... Have a star as I will remember this for future work i do... and a star for Loomah as well otherwise my code would not have got this far!!

Thanks...