Forum Discussion

David Woodley's avatar
David Woodley
Copper Contributor
Sep 25, 2018

Changing th colout of a rage of cells along a row, depending upon the contents of a cell in that row

Hi All.

 

I wish to  write a spreadsheet with the following properties............

If there is no data in the row, then all cells are white (I.e. no fill)

If there is data in cells A2 to G2  then cells A2 to G2 are filled red with white ink,

If there is data in cells H2 to I2, then cells A2 to J2 are yellow with black ink. 

If there is data in cells K2  to L2 then cells A2 to L2 are blue with white ink

If there is data ion cell M2 then cells A2 to M2 are green with white ink

 

I presume that this will require a macro. I have never written a macro before. The actual colours are immaterial. I need each of the above examples to be obviously different with visible text colours within the filled cells

 

This format applies to all rows down to about row 1000

 

Row 1 will have column titles which I can fill in the usual way.

 

Can anybody please help?

 

Thank you in advance

 

David

Excel
Macros and VBA
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Oct 02, 2018

    Hi David,

     

    It's not clear some cells with data or all of them in the range and what are priorities if data in few/all ranges. In general that could be done with conditional formatting like

    and attached

    • David Woodley's avatar
      David Woodley
      Copper Contributor
      Oct 03, 2018

      Thank you for your reply.

      l could not get your formulae to work. I assume that this is because there are different data types in each column

      Column A to C are text strings, 

      Column D is a date

      Column E and F are test strings

      Column G is a date

      Columns H, I and J are text strings

      Column K is a date

      Column L is a text string

      Column M is a date

       

      I suppose the most imprtant column in the correct order are  M, K, J and G

       

      Does this info help?

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Oct 04, 2018

        Hi David,

         

        1) Yes, the order is correct, from M to G

        2) It doesn't matter which type of data do you have in your cells, COUNTA calculates all not empty cells

        3) Rules will be the same for all your rows, it'll be 10 of them or 1000. You only shall have the proper range in Apply to

        4) The formula rule in your example looks like

        actually you have some text, not formula

        It shall be

        5) Since your logic is to color if ALL cells in the range have data, formula will be like

        =COUNTA($H2:$J2)=3

        where 3 in number of the columns in the range

        Finally like

        and attached

Resources