Forum Discussion

MaxCA1000's avatar
MaxCA1000
Copper Contributor
Nov 23, 2021

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 same column as the data that generates them.

 

One way to pick out the subtotals would be by the contents of the adjacent cell, so I'm thinking of something like this:

 

If (A4 IsNull, then A5 format = Bold)

 

But I don't know how to that.

excel
Macros and VBA

5 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 23, 2021

    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. 

    • MaxCA1000's avatar
      MaxCA1000
      Copper Contributor
      Nov 23, 2021
      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.
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Nov 23, 2021

        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.

         

Resources