Conditional formatting using VBA

[flemming94]

Thanks to security settings on a file at work, I can't use conditional formatting, but I can use macro's (I know, kinda stupid). So I'd like to make an easy macro to make empty cells red in a specific column (A7 through A100). I'm kinda new to the VBA project and can't figure it out. Can someone help me?

 

[Jonmo1]

Welcome to the board.

something like this

Sub ColorBlanks()
Dim c As Range
For Each c In Range("A7:A100")
    If c.Value = "" Then
        c.Interior.Color = vbred
    Else
        c.Interior.ColorIndex = xlnone
    End if
Next c
End Sub

 

[flemming94]

Many thanks

It seems it works in an unprotected sheet, but not in the protected one... Seems like they checked an extra option so no lay out can be changed.

 

[Jonmo1]

You can unprotect the sheet, run the code, then reportect the sheet..

Sub ColorBlanks()
Dim c As Range
ActiveSheet.Unprotect "passwordgoeshere"
For Each c In Range("A7:A100")
    If c.Value = "" Then
        c.Interior.Color = vbred
    Else
        c.Interior.ColorIndex = xlnone
    End if
Next c
ActiveSheet.Protect "passwordgoeshere"
End Sub

 

[flemming94]

Problem is that it's an official document from work and I don't know the password (and I'm not supposed to know) But I can certainly use this on other projects. Thanks!

 

[Jonmo1]

You're welcome.