INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel VBA - Cannot Set Color to RGB(#,#,#) value for one cell when others work?

Excel VBA - Cannot Set Color to RGB(#,#,#) value for one cell when others work?

Excel VBA - Cannot Set Color to RGB(#,#,#) value for one cell when others work?

(OP)
I put this together last week, and in what I remember, it worked fine.

Today, suddenly, in one cell, it seems to not work. It fails at Cell(5,5). I've highlighted the line of code that is throwing the error. It's within my error handler:

CODE --> VBA

Sub MarkBadPaths()
On Error GoTo ErrHandler
' Purpose: Highlight bad paths as dark gray to show not valid

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim x As Integer 'row
    Dim y As Integer 'column
    
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("CrossTableUpdates")
    
    For x = 2 To ws.Range("A65000").End(xlUp).Row
        For y = 5 To 8  'can be changed later to be more flexible
            If y = 6 Then 'skip this column for now, Dir() function doesn't like named network shares
            Else
                ws.Cells(x, y).Select
                If Dir(ws.Cells(x, y).Value, vbDirectory) = vbNullString Then
                ' If Dir() evaluates to vbNullString (""), then it's an invalid folder/directory address.
                    ws.Cells(x, y).Interior.Color = RGB(166, 166, 166) 'Gray
                    Debug.Print ws.Cells(x, y).Address & "is not valid"
                End If
            End If
        Next y
    
    Next x

ExitSub:
    Set ws = Nothing
    Set wb = Nothing
    Exit Sub

ErrHandler:
    If Err.Number = 52 Then 'Bad file name or number
        ws.Cells(x, y).Color = RGB(166, 166, 166)
        Resume Next
    Else
        MsgBox "Error number = " & Err.Number & _
                Err.Description _
                , vbCritical, "Error"
        Resume ExitSub
    End If

End Sub 

The step basically says if the code throws me a bad file name or number error, I color that cell dark gray and move on to the next. The idea of the code is to show at a glance what network paths are valid vs invalid.

Thanks for any thoughts.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Excel VBA - Cannot Set Color to RGB(#,#,#) value for one cell when others work?

I am not sure, but a little research showed me that ws.Cells(x,y).Interior.Color = RGB(166,166,166) should work.

RE: Excel VBA - Cannot Set Color to RGB(#,#,#) value for one cell when others work?

(OP)
Hmm... I wonder if I somehow undid that portion. That's right - I believe it does require Interior.Color which I did have in other places in code. Thanks! I'll verify in a little bit and post back.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Excel VBA - Cannot Set Color to RGB(#,#,#) value for one cell when others work?

(OP)
Indeed, that was the problem. Thanks again.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close