Format cell and adjacent cell

[davethewelder]

Hi, I am trying to put a conditional format to format a cell plus the
adjacent cell immediately to the right with a heavy black border round the
two cells. I have tried the code below but I get an error message
"Appplication defined or Object defined error". The code ran previously but
it did not reference the cell or apply the border format. I' ve ran out of
ideas so any help would be apprecieated.

Davie


Sub Borders()
'

Cells.Select
Range("A12").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$B$66"
With Selection.FormatConditions(1).Borders

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

End Sub

 

[Mike H]

Try

Sub marine()
Range("A12:B12").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$B$66"
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Mike

 

[davethewelder]

Mike, thanks for the quick response. I tried your code but still get the
same error message. Also the range A12 highlighted the whole sheet which
allows the conditional format to be applied to the value of B66 in each of
the seven lists on the sheet. When the macro is run I never know where the
listed client will be therefore I cannot use a absolute reference.

Davie

 

[davethewelder]

Hi, I have progressed this to have the macro find the correct cell and put
the border round this cell and the adjacent cell for the first row. The line
of code I use is

Set rng = Range("B2:B20")
For Each cell In rng
If cell.Value = Range("AE1") Then
Range(Cells(cell.Row, 2), Cells(cell.Row, 3)).Select
and then the formatiing.

When I change the rng to ("F2:F64") then the border format highlights the
correct cell but extends from cells "Bx:Fx"
How can I get it to go from "Fx:Gx"?

thanks in advance
Davie