Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Cell Formatting Question (Excel 2000)

    Hi

    In the worksheet attached I have some code supplied by Legare and adapted with the help of Hans.

    The code works fine When I enter Alan in L1 it appears in A1 formatted by the code, but if I delete the word Alan, Alan is removed from A1 but the cell is still formatted,
    the question is, is it possible to remove the fomatting of the cell A1 if Alan is removed from L1
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Formatting Question (Excel 2000)

    Try something like this:

    For Each oCell In Intersect(Target, Range("A1:B12"))
    Select Case oCell.Value
    Case "Alan"
    oCell.Interior.ColorIndex = 9
    oCell.Font.ColorIndex = 2
    Case "Asit"
    oCell.Interior.ColorIndex = 5
    oCell.Font.ColorIndex = 2
    ...
    Case Else
    oCell.ClearFormats
    End Select
    Next oCell

    You can, of course, add more cell formatting attributes if you like.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Cell Formatting Question (Excel 2000)

    Hi

    Thanks for the promt reply, I have tried your suggestion, but the cell interior format still stays if I remove Alan from L1

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Formatting Question (Excel 2000)

    It works for me. Check carefully that you have modified the code correctly.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Cell Formatting Question (Excel 2000)

    Hi Hans

    If i delete A1 it works as you suggest, but i would like if to clear if I delete L1, as A1 is a formula i.e. =L1

    Hope this is clearer.

    Many thanks Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Formatting Question (Excel 2000)

    Your code checks whether a cell in the range A1:B12 has changed. If you want it to work if L1 changes, you should adapt it accordingly. But it could become very messy. Why not enter Alan (or whatever) directly in A1?

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Cell Formatting Question (Excel 2000)

    HI Hans

    Unfortunatley this is not my spreadsheet, I was trying to resovle it for another user, However I have taken your advice on board and I will suggest he does it as you suggest.


    Many thanks for your input and advice

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Formatting Question (Excel 2000)

    The alternative would be to use the Worksheet_Calculate event instead of the Worksheet_Change event, but since Calculate does not have a Target argument, you'd be doing a lot of superfluous work.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Cell Formatting Question (Excel 2000)

    Hi Hans

    I am always minded to to take your expert advice.

    Thanks very much.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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