Fomatting cells

  • Is there a way to conditionally format a cell based on a conditional formatting of another cell?




  • I guess I can't since when I tried, I realized that I would only create a circular reference! Duh! Please forgive the stupidity!



  • Blue,

    I'm pretty sure you can achieve this using VBA......

    say you want A1 to mirror the conditional formatting that's in Z1, then just replicate the conditional formatting formula

    if, however, you want, say, A1 to be bolded IF the conditional formatting in Z1 results in Z1 being green, then VBA should be able to take care of this for you as it can look at cell formats, borders, fonts etc etc etc

    am I on the right track ?

  • Thanks, Chris! Actually I felt a little bit stupid yesterday in my request. I handled what I was trying to achieve via code in some locations and via conditional formattting to screen any text in locations that I don't want text visible given a certain condition. But now I see when I print the workbook out, the text of course prints. I need to mask this as well and since I am trying very hard to REDUCE the code in this huge workbook, do you know of any way sans code to mask the printed version?

    Many thanks!



  • sounds like you may have painted yourself into a bit of a corner.....

    this usually suggests that somewhere earlier in the design / thought process, you've taken the wrong track (not that I've never done this myself, so I'm not judging !)

    I've been looking at a solution for about half an hour and can't come up with anything quick and global within the limits of my beginners-level VBA skills

    how large is your print area ? I suppose someone with better skills may be able to select the print area and test each cell for the results of the conditional formatting and maybe adjust the font color and background color to match......

    another, very clunky and inefficient method may be to similarly isolate those cells in question and insert an image of a similarly-coloured cell via the camera, with its print properties set to TRUE.... effectively hiding the cells underneath when printing occurs with before_print event, but if your range is big you're going to notice a severe performance degredation (been there also!)

    in terms of screen-viewing versus printing.... could you afford to have another sheet that is just linked rigidly to this sheet, for printing purposes ? maybe you could than again apply some formula that will totally blank out the cells you don't want to print (I know you're invoking conditional formatting, so this probably won't work)

    Long term, I'd suggest you revisit your design from scratch if this isn't easily solved - but I know what it's like when you build in escape routes only for them to totally closed by bosses and colleagues who want the Nth degree from you without the faintest idea of what you have to go through to achieve their demands !!

    ANyhow, hope some of this helps, albeit if most of it is negative....... there's some much smarter guys than me around on this forum - I'm sure they'll be able to mull it over too



  • Thanks for your efforts, Chris!!! I really do appreciate that. Yes I have painted myself into a corner - certainly not the first time. We are talking a good sized area here with lots of check boxes and list boxes, etc. I just went thru the first sheet and replaced all my Active-X controls with Excel form tools and data validation with the use of conditional formatting to improve the physical appearance.

    Quite a few people in the company use this spreadsheet so I want it to look good. However, a printed version in black and white is always created for our files and I want that to look good as well.

    I think I need to resort to code to zero out all the list boxes prior to printing. I can't think of anything else to do.

    Feel free, anyone out there who would like to weigh in!



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!