Automatically add thick borders if cell is nonblank

[Skovgaard]

Hi All,

After searching the Internet, not finding a solution, I hope one of you experts are able to help me.

My Challenge:
I have a worksheet containing a roadmap for project launches. The roadmap is a mix of "blank" and nonblank cells (D5:AE17), meaning all cells have a formula in it. Whether it is a "blank" cell or not, is determined by input from another worksheet. When updating this Input-Worksheet e.g. changing a launch date, the visual roadmap is also changing.
Depending on the project status, the borders around the cell is colored red, yellow or green with conditional formatting. This works as it should (the colors are not correct on below picture).

What I would like to, is to make the borders thicker (as you can see below, the colored borders are thicker than the black ones) - Is this possible?

I have tried with a Change_Event, but must admit that my VBA skills are not developed enough yet

/Skovgaard

 

[jkpieterse]

Conditional formatting cannot change the thickness of a border as you've discovered. So if you insist on wanting thicker borders for the colored cases you'll have to revert to VBA.
I would define styles for each color you want (including the empty case) as I explain in this article: Excel: Styles in Excel You can then loop through the cells, test which color you want the cell to get using the logic you currently have in the COnditional formatting and then change the cell's style accordingly.

 

[Skovgaard]

Conditional formatting cannot change the thickness of a border as you've discovered. So if you insist on wanting thicker borders for the colored cases you'll have to revert to VBA.
I would define styles for each color you want (including the empty case) as I explain in this article: Excel: Styles in Excel You can then loop through the cells, test which color you want the cell to get using the logic you currently have in the COnditional formatting and then change the cell's style accordingly.

Hi Jan,
Thanks for your reply.
Haven't really thought of styles like that before, definitely something I'll have in mind for the future.

I have now created my styles, so VBA is next.
As I wrote, my VBA skills isn't the best, trying to self educate myself. Could you point me in a direction - How should/can the code be run? Should it be manual or can I use a change event?

/Skovgaard

 

[jkpieterse]

It depends on how the changes to the cells in question are triggered. If all of these cells contain formulas, you would have to use a change event on the sheet that contain the cell(s) which are part of the calculation. Please note however, that changing the style of a cell using VBA will zap the undo history so your users end up with no undo.

 

[Skovgaard]

I have now created below VBA, and seems to be working if the Vlookup value isn't an error. But unfortunately it doesn't apply a cell style, if the value in the cell is blank (all cells contain a formula).
How can I change my code, so if either Vlookup is error or cell value is empty, then cell.style will change to "StyleEmptyProject"?

Sub RefreshStyles()
    
Dim rRoadmap As Range, rInput As Range, cell As Range
Dim sStyle As String
Dim wsLaunchMap As Worksheet

Set rRoadmap = Worksheets("Launch map").Range("D5:AE17")
Set rInput = Worksheets("Input").Range("A:E")
Set wsLaunchMap = Sheet1

With wsLaunchMap
    On Error Resume Next
    For Each cell In rRoadmap
        If cell <> "" Then
            If Trim(cell.Value) <> "" Then
                sStyle = Application.WorksheetFunction.Vlookup(cell, rInput, 5, False)
                cell.Style = "Style" & sStyle
            Else
                cell.Style = "StyleEmptyProject"
            End If
        End If
    Next cell
End With

End Sub

/Skovgaard

 

[jkpieterse]

You could do something like this:

Sub RefreshStyles()

    Dim rRoadmap As Range, rInput As Range, cell As Range
    Dim sStyle As String
    Dim wsLaunchMap As Worksheet

    Set rRoadmap = Worksheets("Launch map").Range("D5:AE17")
    Set rInput = Worksheets("Input").Range("A:E")
    Set wsLaunchMap = Sheet1

    With wsLaunchMap
        On Error Resume Next
        For Each cell In rRoadmap
            If Len(cell.Value) > 0 Then
                If Trim(cell.Value) <> "" Then
                    sStyle = ""
                    sStyle = Application.WorksheetFunction.VLookup(cell, rInput, 5, False)
                    If Len(sStyle) > 0 Then
                        cell.Style = "Style" & sStyle
                    Else
                        cell.Style = "StyleEmptyProject"
                    End If
                Else
                    cell.Style = "StyleEmptyProject"
                End If
            End If
        Next cell
    End With

End Sub

But this probably does not address the issue with the empty cells (which probably aren't empty?)

 

[Skovgaard]

No unfortunately this is the same, it doesn't solve my issue with "blank" cells.

If you look at my picture from my first post, all cells in D5, D7, D9... and F5, F7 etc. have a formula in it. The formula in the cells, starts with an =IFERROR(VLookUp(.....),""). So if the blank ("" from IFERROR) is TRUE in the cell, the "normal" style should be applied to the cell in VBA.

Is this possible?

/Skovgaard

 

[Skovgaard]

I found a solution. Instead of displaying "" in my IFERROR, I changed it to " " (a space), then I could easily point out all that equals to " ".

Thanks for your guidance Jan.

/Skovgaard