Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell and adjacent cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell and adjacent cell
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell and adjacent cell
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 "Mike H" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell and adjacent cell
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 "davethewelder" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format of adjacent cell with different range and crite | New Users to Excel | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
change current cell colour based on the value of adjacent cell on other worksheet | Excel Programming | |||
Format cell based on adjacent cells values | Excel Programming | |||
I want to format a cell based on an adjacent cells value | Excel Discussion (Misc queries) |