Cell formatting based on cell value

[77highland]

Hello everyone - First ever post for me.

I have a worksheet that looks like the following:


[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Date Occurred
[/TD]
[TD]Asset Number
[/TD]
[TD]Coach Number
[/TD]
[TD]Fault Description
[/TD]
[TD]Priority
[/TD]
[/TR]
[TR]
[TD]22/11/13
[/TD]
[TD]395001
[/TD]
[TD]39016
[/TD]
[TD]Headlight Failure
[/TD]
[TD]1b
[/TD]
[/TR]
[TR]
[TD]13/09/13
[/TD]
[TD]395001
[/TD]
[TD]39014
[/TD]
[TD]HVAC Vibration
[/TD]
[TD]4a
[/TD]
[/TR]
[TR]
[TD]02/11/13
[/TD]
[TD]395009
[/TD]
[TD]39095
[/TD]
[TD]Window Smashed
[/TD]
[TD]2a
[/TD]
[/TR]
[TR]
[TD]05/11/13
[/TD]
[TD]395009
[/TD]
[TD]39092
[/TD]
[TD]Brake Pad Change
[/TD]
[TD]1a
[/TD]
[/TR]
[TR]
[TD]08/11/13
[/TD]
[TD]395015
[/TD]
[TD]39154
[/TD]
[TD]Toilet Fault
[/TD]
[TD]2b
[/TD]
[/TR]
</TBODY>[/TABLE]










I need to automatically apply a thick bottom boarder to each row of the final group of asset numbers. I.e:- Underline row 3 as 3b contains the final '395001' and row 5 as 5b contains the final '395009'.

This data is forver changing so would be useful if I could run a macro (or CF?) using an on/off button or tick box.

Thanks.

 

[L. Howard]

Hi 77, welcome to the forum.

Give this a try. Note that final group asset number may be a single number.

Regards,
Howard

Option Explicit

Sub uLineIt()

Dim c As Range
Dim lRow As Long

lRow = Range("B2:B6").End(xlDown).Row

Range("A2:E" & lRow).Select
  Selection.Borders(xlEdgeBottom).LineStyle = xlNone
  Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Application.ScreenUpdating = False

For Each c In Range("B2:B" & lRow)
  If c.Offset(1, 0) <> c Then
    c.Offset(0, -1).Resize(1, 5).Select
    
      With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
      End With

  End If
  
Next
Application.ScreenUpdating = True
End Sub

 

[bschwartz]

This can also be done with conditional formatting, except that conditional formatting doesn't have the option for thick borders. If you wanted regular borders, you could do something like this:

Excel 2010
	A	B	C	D	E
1	Date Occurred	Asset Number	Coach Number	Fault Description	Priority
2	22/11/13	395001	39016	Headlight Failure	1b
3	13/09/13	395001	39014	HVAC Vibration	4a
4	2/11/2013	395009	39095	Window Smashed	2a
5	5/11/2013	395009	39092	Brake Pad Change	1a
6	5/12/2013	395009	39092	Brake Pad Change	1a
7	8/11/2013	395015	39154	Toilet Fault	2b
Sheet105

1) Highlight the entire range
2) Go to conditional formatting --> New Rule --> Use formula
3) Use this formula: =$B2<>$B1
4) Select Format --> Border --> Top border
5) Ok, Ok, Ok.