How to check the property of cell in an array in Excel VBA

jnsimex
jnsimex used Ask the Experts™
on
Hi Experts,

I have set a range as an array in Excel VBA and I would like to know the font color of an array element.

How do I accomplish this? here is the code I am trying to use:

Thanks Experts!


Dim v() As Variant

Dim rng As Range

Sheets("COA").Select

'Declare array
v = Range("D5:G3840")

Sheets("SimEx BS").Select

'Get the lastrow
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
    If (Cells(i, "A").Value = (v(1, 1))) Then
        If (v(1,1).Font.Color = vbBlack) Then <- Object required
            MsgBox "black"
        End If
    End If
Next i
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Define v like this
Dim v As Variant

and change

v = Range("D5:G3840")

to

Set v = Range("D5:G3840")

Author

Commented:
Thank you!!
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial