Create border around cells with different values using either VBA or conditional formatting

[kelly mort]

In column AF, I have numbers or strings (the cells are formatted as text) 1 or 2.

It follows like this:
1
2
1
2
......

So for the first set, from Col B to Col AE (Excluding Col U and V), I want to compare the values for the corresponding 1 and 2 rows.

If they are different, then I want a border around those cells.

For example
B4 = Cat, ......, AF4 = 1
B5 = Cats, ....., AF5 = 2

the above represent "a set" as described before.

And in this case, I want a border around B4:B5.

Then I check all other "twin cells" as explained earlier.

How do I achieve that?

Thanks in advance.
Kelly.

 

[Toadstool]

Hi Kelly Mort,

Here is a Conditional Format approach.

The formula

=AND($AF4="1",OR(OFFSET($B4,,,,19)<>OFFSET($B4,1,,,19),OFFSET($W4,,,,9)<>OFFSET($W4,1,,,9)))

builds the left, top, right line for the box.
the formula

=AND($AF4="2",OR(OFFSET($B4,,,,19)<>OFFSET($B4,-1,,,19),OFFSET($W4,,,,9)<>OFFSET($W4,-1,,,9)))

builds the left, bottom, right lines for the box

Columns G to AD are hidden but all contain nulls.

Kelly Mort.xlsx
	A	B	C	D	E	F																									AE	AF	AG
3
4		Cats																														1
5		Cat																														2
6		Dog																														1
7		Dog																														2
8		Sheep																														1
9		Sheep			Pig																											2
10		Goat		Cow																												1
11		Goat		Cow																												2
12		Horse																													Pig	1
13		Horse																													Pig	2
14
15
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B17	Expression	=AND($AF4="2",OR(OFFSET($B4,,,,19)<>OFFSET($B4,-1,,,19),OFFSET($W4,,,,9)<>OFFSET($W4,-1,,,9)))	text	NO
B4:B20	Expression	=AND($AF4="1",OR(OFFSET($B4,,,,19)<>OFFSET($B4,1,,,19),OFFSET($W4,,,,9)<>OFFSET($W4,1,,,9)))	text	NO

 

[kelly mort]

@Toadstool , where should I place the formula?

When I enter it through the "use a formula to determine which cells to format" rule, it throws an error message at me saying there is a problem with the formula.

 

[Toadstool]

@Toadstool , where should I place the formula?

When I enter it through the "use a formula to determine which cells to format" rule, it throws an error message at me saying there is a problem with the formula.

You need to use that little icon to the right of each formula "Copy to clipboard" to get the formula, then select cells B4 and down as far as you want.
Use New Rule or Manage Rules and "Use a formula to determine which cells to format" and in "Format values where this formula is true" paste in the formula from the clipboard.
For format select Borders and the left, top and right. Repeat these steps with B4 down still selected but use th 2nd formula and a border format of left, bottom, right.
NOTE: You said column AF contained a 1 or a 2 in text. If it's actually numeric then just change that "1" and "2" in the formulae to 1 and 2.

 

[kelly mort]

Okay it's working now.

But it does not create the borders for the other columns C:AE excluding U and V.

When C4 and C5 have different values, the rule should set borders around them and same should apply throughout to AE excluding U and V..

 

[Trebor76]

Hi Kelly,

The following does the job for each pair of rows in the tab 'ws' - it does not look at the 1 or 2 flag and case sensitivity is also ignored so not sure with those caveats the following provides a suitable solution:

Option Explicit
Sub KellyMort()

    Dim ws As Worksheet
    Dim i As Long, j As Long, x As Long
    Dim r As Range
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Sheet containing the data. Change to suit if necessary.
    
    On Error Resume Next 'Ignore error if there's no data in 'ws'
        j = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If j < 4 Then
            MsgBox "The expected first data row is four." & vbNewLine & "Please check """ & ws.Name & """ and try again.", vbExclamation
            Exit Sub
        End If
    On Error GoTo 0
    
    For i = 4 To j Step 2
        For x = 2 To 31 'Columns B to AE
            If x <> 21 Or x <> 22 Then 'Ignore columns U and V
                Set r = Range(ws.Cells(i, x), ws.Cells(i + 1, x))
                If StrConv(ws.Cells(i, x), vbUpperCase) = StrConv(ws.Cells(i + 1, x), vbUpperCase) Then
                    r.Borders.LineStyle = xlLineStyleNone
                Else
                    r.BorderAround Weight:=xlThin
                End If
            End If
        Next x
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[kelly mort]

I like the vba approach.

Thanks @Trebor76 for it. And thanks @Toadstool for your time.

Have a wonderful weekend.

 

[Trebor76]

You're welcome and I hope you have a great weekend too.

 

[Toadstool]

Kelly Mort, I am pleased you have a found a solution.
Just to answer your last question, you can change the Conditional Format "Applies to" to extend which cells get the borders.