checking for formatting

Discussion in 'Microsoft Office Specialist (MOS), Office 365' started by sirdragon, Jun 6, 2005.

  1. sirdragon

    sirdragon Nibble Poster

    Hi guys

    can anyone figure this one out?
    I am sure it is possible but cannot think of the syntax.

    I would like to be able to check column A for internal cell formatting,(Cell.Interior.value?)

    If has formatting then the next cell in the same row should contain "OT". (ColOffSet ? cell.value="OT")

    If not it should remain blank. ("")

    then the formatting should be removed from the cell in column A. Leaving just the content.(cell.Interior.value="")

    rather annoying this one

    cheers martin
  2. sirdragon

    sirdragon Nibble Poster

    I have tried various ways around this so far to no avial.

    Lets see if I can explain it in more detail as that may jog a few grey cells.

    I am running a prog in excel that autofilters for a variety of criteria, if it finds them it copies and paste them to another location.

    part of the programe, is a conditional format that colours the interior of a cell grey if it is changed.:oops:

    this is the formatting that i wish to copy with the cells, but I do not want the rest of the cell content just the formatting.

    I can do this with a paste sepecial that copies everything them selectively removes elements that it as just copied leaving just the formating but this is rather long winded and a waste of code.

    from getting this formating I then need a way of the code to recognise that the code is there and put a value of "OT" in the next row. Indicating that the choice is overtime.

    so in a nutshell

    1) can I copy conditional formating from one cell to another without the rest of the data?

    2) how do you get code to recognise that a cell is shaded?

    any ideas on these two items much appreciated.

    cheers martin.
  3. Viv Fenge

    Viv Fenge New Member

    Go back to the beginning and ask why you have conditional formats.

    Certifications: ecdl, databases CLAIT 2
    WIP: MOS
  4. sirdragon

    sirdragon Nibble Poster


    tried that too

    need the conditional formatting as that is how the guys at work want to be able to make sure that the "OT" overtime is SEEN, to be in place.:blink

    purely a visual check that they like. My problem is that, that's the only way I have to determine that it IS overtime and so distinquish between it and normal rate, from the rota sheet.

    I think I will have to go the long route for now untill I can find a short cut to get rid of some of the excess coding required to leave just the formatting behind.

    I have an IF to work out if the cell is formatted.
    If Worksheets("sheets").Range("k12").Interior.ColorIndex< 0 Then
    Range("k4").Value= "OT" 
    so it is just the formatting :dry that is causing the problem at the moment, and of course cleaning the whole thing up a bit lol

    cheers martin

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.