Format cell and adjacent cell

D

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
 
M

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
 
D

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loops through Sheets but doesn't work. 5
Formatting stop when reach blank cell 6
Help with macro 1
Hlp! Adv Filter - Non-Contiguous rows don't copy 11
AutoFilter 7
problem with code 3
Cell Border 1
Borders Object 3

Top