+ Reply to Thread
Results 1 to 3 of 3

Cell Formatting Conditional On Other Cells Fill Color?

  1. #1
    Kael_Sidhe@yahoo.com
    Guest

    Cell Formatting Conditional On Other Cells Fill Color?

    I'm not sure of the best way to go about this. First let me say that I
    am a basic though long time Excel user. I can use basic Excel
    functionality but I wouldn't even know where (much less how) to begin
    to write VB code without very detailed instructions.

    I am trying to create conditional formatting based on a gantt type
    chart. This chart may not line up correctly using variable width fonts
    but consider the following;

    ABCDEF
    1 GORO
    2 BBB
    3 BBB
    4 BB

    There are no cell values, just cell formatting. The cell values in the
    above example represent color filled cells. (B=BLACK, W=WHITE, G=GREEN,
    O=ORANGE, R=RED)

    In row #1 I want to color each cell based on the number of color filled
    cells found in the column below it. So A1 would be white because there
    are no colored cells in column A. B1 would be green because there is 1
    black cell in the column. C1 would be orange because there are 2 black
    filled cells under it and D1 would be red because there are 3 colored
    cells under it.

    I want to be able to highlight cells in a given row and select a black
    color fill and have row #1 automatically update itself based on the new
    total number of colored cells below it.

    I am very comfortable following very detailed instructions but sparse
    references will quickly get me lost.

    Thank you in advance!
    Roger Westbrook


  2. #2
    Peter T
    Guest

    Re: Cell Formatting Conditional On Other Cells Fill Color?

    Hi Roger,

    I would suggest instead of formatting your cells in row 2 down with black
    and a code solution, do everything with conditional formats.

    Select A2, Format > Conditional Formats

    Change Cell value is to Formula is
    =LEN(A2)>0
    Apply a Pattern colour, eg Black
    Copy A2 to A2:E10

    Type something in any of these cells, should change to black. Delete the
    cell, should revert to white or the original cell colour format.

    Add 3 CF conditions in cell A1, each Formula is
    =COUNTA(A$2:A$10)>=3 | Red
    =COUNTA(A$2:A$10)=2 | Orange
    =COUNTA(A$2:A$10)=1 | Green

    Copy A1 to A1:E1

    Ensure the $ are exactly as above and should be no quotes - go back and
    double check.

    If you decide to change your black, change the font to same colour.

    Regards,
    Peter T


    <Kael_Sidhe@yahoo.com> wrote in message
    news:1144270053.819310.30790@t31g2000cwb.googlegroups.com...
    > I'm not sure of the best way to go about this. First let me say that I
    > am a basic though long time Excel user. I can use basic Excel
    > functionality but I wouldn't even know where (much less how) to begin
    > to write VB code without very detailed instructions.
    >
    > I am trying to create conditional formatting based on a gantt type
    > chart. This chart may not line up correctly using variable width fonts
    > but consider the following;
    >
    > ABCDEF
    > 1 GORO
    > 2 BBB
    > 3 BBB
    > 4 BB
    >
    > There are no cell values, just cell formatting. The cell values in the
    > above example represent color filled cells. (B=BLACK, W=WHITE, G=GREEN,
    > O=ORANGE, R=RED)
    >
    > In row #1 I want to color each cell based on the number of color filled
    > cells found in the column below it. So A1 would be white because there
    > are no colored cells in column A. B1 would be green because there is 1
    > black cell in the column. C1 would be orange because there are 2 black
    > filled cells under it and D1 would be red because there are 3 colored
    > cells under it.
    >
    > I want to be able to highlight cells in a given row and select a black
    > color fill and have row #1 automatically update itself based on the new
    > total number of colored cells below it.
    >
    > I am very comfortable following very detailed instructions but sparse
    > references will quickly get me lost.
    >
    > Thank you in advance!
    > Roger Westbrook
    >




  3. #3
    Kael_Sidhe@yahoo.com
    Guest

    Re: Cell Formatting Conditional On Other Cells Fill Color?

    Thank you Peter that works perfectly and seems better than the way I'd
    intended. Thanks again and have a great day!

    Roger Westbrook


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1