formatting

[p please bob]

I am trying to format a cell (place solid outline around it) if another cell in that row isn't blank. However the column of possibly filled cells varies from worksheet to worksheet.
This is driving me so nuts I can't explain it properly either. all help gratefully rec'd

[walding]

Do the columns of "possibly filled cells" have anything in common (e.g. header at the top, value if filled) that is exclusive to those columns?

It's difficult to visualise what you are trying to do.

Andrew

[p please bob]

I have sub totalled a range of information, where the **** total appears in a row, say cell D5 - I want to format cell J5 with a solid outline.

[walding]

Conditional formatting won't work, as far as I know.


It can be done with a macro, but that would either mean running the macro on an event (e.g. every time you change cell selection - this could be limited to checking just that row) or doing the check manually (e.g. press a button).
How this is scripted depends on how you want it to function.

[p please bob]

The info changes daily, it is sorted and then printed, the formatted cells give a 'tick box' on the hard copy, so a macro would probably be ok, but I don't know where to start.

[walding]

This will need editing to suit your needs.
Try it and see what changes you want. If you need more help, just ask.


Sub format()

' Declare Variables
Dim cellToFormat
Dim cellToCheck
Dim columnToFormat
Dim columnToCheck
Dim rowNumber

'Set Column to Format
columnToFormat = "J"

'Set Column to Check
columnToCheck = "D"

'Set Number of Rows to Format
rowNumber = 1000

' For loop to check D column and format J column
For i = 1 To rowNumber

cellToCheck = columnToCheck & i

If Not (Range(cellToCheck).Value = "") Then
cellToFormat = columnToFormat & i
With Range(cellToFormat).Borders(xlEdgeLeft)
End With
With Range(cellToFormat).Borders(xlEdgeTop)
End With
With Range(cellToFormat).Borders(xlEdgeBottom)
End With
With Range(cellToFormat).Borders(xlEdgeRight)
End With
End If

Next i


End Sub

[walding]

Apologies - missed out the weighting of the borders:

.Weight = xlThin
or

.Weight = xlThick

This line goes directly under each border setting:

With Range(cellToFormat).Borders(xlEdgeLeft)
.Weight = xlThin

[p please bob]

Thanks very much, I'll try it out and let you know when I get back to work.

I wish I'd asked here first !!

[Ian Mac]

On 2002-05-17 01:54, walding wrote:
Conditional formatting won't work, as far as I know.


It can be done with a macro, but that would either mean running the macro on an event (e.g. every time you change cell selection - this could be limited to checking just that row) or doing the check manually (e.g. press a button).
How this is scripted depends on how you want it to function.

Why wil conditional formatin not Work??

from your code it looks like:

in column J 1-1000

Conditional Formating>Formula is =ISNUMBER($D1)

should be the same.

With regards to the orignal question.
Is the formatting only to be done in the row with a SUBTOTAL with other numbers above?

[walding]

On 2002-05-17 05:04, Ian Mac wrote:

Why wil conditional formatin not Work??

from your code it looks like:

in column J 1-1000

Conditional Formating>Formula is =ISNUMBER($D1)

should be the same.

I don't know why it won't work - I just can't get it to work! Let me know if you manage, and how you did it - I've tried without success.

Cheers

Andrew