Forum Discussion
Conditionally format according to the content of another Cell
MaxCA1000 If the sub-total rows have a common header like "Sub-total", you could put a filter on the sheet and select all sub-total rows, format them to your liking and then deselect the filter. Alternatively, if the sub-total rows contain formulae (like SUM, I presume) and the other rows are just "flat" numbers, select the entire range containing sub-totals, press F5, Special..., Select Formulas, OK and then Ctrl-B.
If this doesn't work, you can perhaps upload a file demonstrating exactly how your sheet is constructed. Remove anything confidential, though.
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.