Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Conditional Formatting Troubles

  1. #11
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    condition 1 is the Match formula
    condition 2 is =MOD(ROW(),2)

    I said in my post earlier that I was highlighting even numbered rows, I meant odd numbered ones.

    thanks to everyone for your help on this.
    giacomo

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-29 12:08, giacomo wrote:
    condition 1 is the Match formula
    condition 2 is =MOD(ROW(),2)

    I said in my post earlier that I was highlighting even numbered rows, I meant odd numbered ones.

    thanks to everyone for your help on this.
    giacomo
    What do you want to happen if a city that must be hilited is in an odd numbered row which has to be hilited too?

  3. #13
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 12:23, Aladin Akyurek wrote:
    On 2002-04-29 12:08, giacomo wrote:
    condition 1 is the Match formula
    condition 2 is =MOD(ROW(),2)

    I said in my post earlier that I was highlighting even numbered rows, I meant odd numbered ones.

    thanks to everyone for your help on this.
    giacomo
    What do you want to happen if a city that must be hilited is in an odd numbered row which has to be hilited too?
    if it's on an odd numbered row I want to highlight it by changing the background color. If it's a matching city I want to bold the text.

    seems like it should be easy but I can't make the two work together.

    thanks.
    giacomo

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-29 12:30, giacomo wrote:
    On 2002-04-29 12:23, Aladin Akyurek wrote:
    On 2002-04-29 12:08, giacomo wrote:
    condition 1 is the Match formula
    condition 2 is =MOD(ROW(),2)

    I said in my post earlier that I was highlighting even numbered rows, I meant odd numbered ones.

    thanks to everyone for your help on this.
    giacomo
    What do you want to happen if a city that must be hilited is in an odd numbered row which has to be hilited too?
    if it's on an odd numbered row I want to highlight it by changing the background color. If it's a matching city I want to bold the text.

    seems like it should be easy but I can't make the two work together.

    thanks.
    giacomo
    I believe we're stuck on this: that's what is mentioned in the Excel Help file regarding conditional_formatting:

    Using multiple conditions If more than one specified condition is true, Excel applies only the formats of the first true condition.

    This means that (a) MOD is already true (>0), when (b) MATCH is also true (>0). So (a) is applied. Changing the order doesn't help.

    Aladin




    [ This Message was edited by: aladin akyurek on 2002-04-29 14:30 ]

  5. #15
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Giacomo,

    how large is the entire data range you want to apply *both* of your conditional formats to ?

    (ie A1 to D20........ small range

    A1 to Z3000....... large range)

    I can think of a "cheat" workaround, but it would only work for a range that is about as big as a normal single print page area....

    it would also only be "viewable" as oppose to being "linkable"



  6. #16
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin - thanks for your help, I guess I can live without the highlighted rows

    Chris - my range is larger than a single printed sheet, A1:A197. But I'd like to hear your solution anyway, you've got me curious.

    thanks
    giacomo

  7. #17
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-29 14:35, giacomo wrote:
    Aladin - thanks for your help, I guess I can live without the highlighted rows

    Chris - my range is larger than a single printed sheet, A1:A197. But I'd like to hear your solution anyway, you've got me curious.

    thanks
    giacomo
    Try the following, which I didn't test thoroughly:

    Condition 1:

    =MOD(ROW(),2)*(LEN(B:B)=0)

    Format for background and font.

    Condition 2:

    =MOD(ROW(),2)*(MATCH(B1,CityList,0))

    Format for background and font.

    Condition 3:

    =MATCH(B1,CityList,0)

    Format just for font.

    Aladin

  8. #18
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Basically, it involves setting up an extra sheet, linked to your data

    your original sheet has your first conditional formatting applied (MOD(row( etc etc)

    the second has your second conditional formatting applied (bold text)

    then you take a snapshot of the 2nd sheet using the camera option

    view
    toolbars
    customise
    commands
    tools.......... scrolldown the options

    click the camera icon and drag it to your toolbar, you should now have a camera sitting on your toolbar

    now highlight your 2nd sheet of cells in question and click the camera, this will take a snapshot of those cells

    now go to your first sheet, top left and click and drag to max size, overlapping the two identically, removing any border and making it see-through so the formats filtrate each other ( right click the image and select format picture)

    if formatted correctly, you should still have your alternate format showing through, with bolded text sitting ontop

    the camera image, however, is only as large as the print page size, so won't work with 180 lines

    maybe you could do 4 sets of 90 lines....

    told you it was fiddly !

    these are also images, so don't sit as useable cells which you can link to affectively.....

    food for thought though if you're desperate....



    :: Pharma Z - Family drugstore ::

  9. #19
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 14:43, Aladin Akyurek wrote:
    On 2002-04-29 14:35, giacomo wrote:
    Aladin - thanks for your help, I guess I can live without the highlighted rows

    Chris - my range is larger than a single printed sheet, A1:A197. But I'd like to hear your solution anyway, you've got me curious.

    thanks
    giacomo
    Try the following, which I didn't test thoroughly:

    Condition 1:

    =MOD(ROW(),2)*(LEN(B:B)=0)

    Format for background and font.

    Condition 2:

    =MOD(ROW(),2)*(MATCH(B1,CityList,0))

    Format for background and font.

    Condition 3:

    =MATCH(B1,CityList,0)

    Format just for font.

    Aladin
    nope, didn't work. I think I see where you were going with that though. I can make it work if only one of the 3 conditions evaluates to true, and the other two are false. right?

    thanks,
    giacomo

  10. #20
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris thanks for your ideas too. I'm off work in about 5 minutes but I'll be sure to try your idea tomorrow.

    thanks,
    giacomo

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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