Conditional Thick Border

[Legacy 287389]

Hi good people!,

I know that we cannot by default apply thick borders around a cell with conditional formatting. I do understand also the principle of negative logic, to make all borders thick, and control the thickness of these borders then with conditional formatting. But, sadly in my case that will not work.

My spreadsheet contains data in cells which are arranged horizontally, vertically and diagonally. If one cell does not match criteria, conditional formatting will give that cell thin borders, which means the cells all around this one cell, which all do match the criteria, will have only 3 thick borders. There are too many IF conditions to write a conditional formatting formula for each cell. I sit with 148 cells.

Next to this block of cells, I have created two columns. Column J contains the criteria, (Either a 1 or a 0), and column K the reference cell. (One of the 148 cells). I have done this because I think VBA can now take over and look for cells which contain a "1", and then apply thick red borders around the reference cell.

Unless there is a different way, could anyone please help with a code that will be able to do this kind of thing?. All and any help will be accepted with great admiration and gratitude. Thank you..

 

[RickXL]

Hi,

I am not sure how much this helps but I have answered a slightly different question

I looked at cells to see if the one to the right was contained the same value as the one to the left. If not then it drew a thick border.
This was repeated for cells and the one underneath.

Sub SetBorders()
    Dim c As Range
    Dim r As Range

    Set r = Range("B6:E17")
    
    For Each c In r
        If c.Value = c.Offset(, 1).Value Then
            Call myBorder(c, xlEdgeRight, "none")
        Else
            Call myBorder(c, xlEdgeRight, "thick")
        End If
        
        If c.Value = c.Offset(1).Value Then
            Call myBorder(c, xlEdgeBottom, "none")
        Else
            Call myBorder(c, xlEdgeBottom, "thick")
        End If
    Next
    With r
        Call myBorder(r, xlEdgeLeft, "thick")
        Call myBorder(r, xlEdgeTop, "thick")
        Call myBorder(r, xlEdgeRight, "thick")
        Call myBorder(r, xlEdgeBottom, "thick")
    End With
End Sub

Sub myBorder(r As Range, edge As Long, t As String)
    With r.Borders(edge)
        If UCase(t) = "THICK" Then
            .LineStyle = xlContinuous
            .ColorIndex = 3
            .TintAndShade = 0
            .Weight = xlThick
        Else
            .LineStyle = xlNone
        End If
    End With
End Sub

Re-reading your question you just need to run:

    With r
        Call myBorder(r, xlEdgeLeft, "thick")
        Call myBorder(r, xlEdgeTop, "thick")
        Call myBorder(r, xlEdgeRight, "thick")
        Call myBorder(r, xlEdgeBottom, "thick")
    End With

in a loop for each required cell?

 

[Legacy 287389]

Hi RickXL,

I have a slightly different scenario here. I work in a casino. What I have designed is one part of our main floor on Excel, bordering the cells as the machines are installed. (The same layout pattern).This is sheet1. In column A are the numbers of these mc's, running from A1 to A145. Now, the allocated denominations of these mc's are on sheet2. I have on sheet1 again a dropdown which houses the distinct denomination values. On sheet1 column B, I have a formula that matches the selected denom in the drop down, to the denoms on sheet 2, and all matches, cause a "1" in column B of sheet1. So, all machines which are running on the selected denom, will have a "1" in the cell, next to it. Now, because the layout of the machines are horizontal, vertical and diagonal, the cells in column B refers to the mc numbers in column A, BUT the actual machine on the layout is in BB15 for example...so the code reads:

IF B1=1 then Range("BB15").BorderAround Weight:=xlThick

Okay, so this is how the specific cells are chosen (Linked) to a changed cell. I really hope you will be able to assist me with this. Thank you very much for your time and efforts.

 

[Legacy 287389]

Hi RickXL,

I'm closing this thread, I have decided to go a completely different route. Thanx for being out there, much appreciated..

 

[RickXL]

No problem.