Forum Discussion
Conditionally format according to the content of another Cell
I want to be able to format subtotals in bold text. There are about 1000 of them in my sheet, so not practical to do it by hand and I can't format the whole column because the subtotals are in the sa...
Hi Riny:
Thank you for your interest. I think you're onto something because each subtotal is named:
=SUBTOTAL(9,E5:E6) The number 9 refers to the action, in this case, sum.
This is what a section of the range summed looks like:
531.00 50.00 26.55
666.00 50.00 33.30
59.85
1383.00 40.00 55.32
1338.00 40.00 53.52
108.84
928.00 30.00 27.84
834.00 30.00 25.02
52.86
The third row is the sum of the previous two rows. Here it's shown offset one cell to the left, an artifact of pasting it, but on the spreadsheet, it's in the same column as the two cells it sums.
I think there's some programmatic way of formatting only those cells with the word SUBTOTAL in the formula, but I don't know how to do that.
Thank you for your interest. I think you're onto something because each subtotal is named:
=SUBTOTAL(9,E5:E6) The number 9 refers to the action, in this case, sum.
This is what a section of the range summed looks like:
531.00 50.00 26.55
666.00 50.00 33.30
59.85
1383.00 40.00 55.32
1338.00 40.00 53.52
108.84
928.00 30.00 27.84
834.00 30.00 25.02
52.86
The third row is the sum of the previous two rows. Here it's shown offset one cell to the left, an artifact of pasting it, but on the spreadsheet, it's in the same column as the two cells it sums.
I think there's some programmatic way of formatting only those cells with the word SUBTOTAL in the formula, but I don't know how to do that.
MaxCA1000 Personally, I'm not a big fan of VBA. Especially not for something as trivial as formatting cells based on its content/nature. And now that I thought about it a bit more, why not use Conditional Formatting to highlight the cells with a formula in a particular range. See attached for an example, where I used a rule =ISFORMULA(D2), applied to column $D$2:$D$10. Perhaps that will work for you.
Edit: Sorry! Wrong file. New one attached.
- Hi Riny:
Thanks again for your suggestion. It was a good one but it wouldn't work because there are numerous formulae on the sheet.
I found a way of doing it using VBA that involves testing to see if a cell in column 3 has a LEN of 0. If it does, that means the cell in column 5 is a subtotal and I can set it to bold. This is the code I used:
Private Sub BoldSubTotals()
Dim iRow As Integer
Dim iCount As Integer
iCount = WorksheetFunction.CountA(Columns(5))
For iRow = 2 To iCount
If Len(Cells(iRow, 3)) = 0 Then
Cells(iRow, 5).Font.Bold = True
Else
End If
iRow = iRow + 1
Next iRow
Exit SubMaxCA1000 Glad you found a solution to your issue. Personally, I'm not very keen on using VBA. I guess Conditional Formatting could have achieved the same. Didn't think of that earlier. Sorry.
See attached.