Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    macro to format part of a cell (2003)

    Hi All,

    I have a spreadsheet where we track dates for deliverables. A col represents a particular type of deliverable - for example, col H might represent security rules while col K might be for router rules. Each row represents a project.

    So cell H3 will be the delivery date of the security rules for whatever project is in row 3.

    Often times, the date of a deliverable will change but I want to keep the old date and show the new date under it. So I edit the cell, say H3, in the formula bar by placing by cursor after the original date, hitting ALT+enter, and entering the new date.

    What I then want to do is have a strikethrough of the original date. While I'm editing the cell, I can new select just the original date in the formula bar, and click on Format, Cells, Font tab, check off the Strikethrough checkbox, and click OK.

    A lot of clicks just to cross out the date.

    I tried recording a macro just after selecting the original date. The macro choice on the Tools menu is grayed out or the entire Macro menu is grayed out (I think I had both behaviors on different computers).

    When I record a macro after leaving the editing mode but not leaving the desired cell, the macro records the keystrokes but applies to the entire cell's contents.

    I'm looking for a macro that will apply the desired formating (in the above, it is strikethrough, but I may have other formats to apply) to just the entire selection of the cell, not the entire cell.

    TIA

    Fred

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to format part of a cell (2003)

    The following will strikeout the original contents of the current selected cell, add a return, and then enter the current day.
    <pre>
    Sub strikeit()
    Dim strOut As String, oldLen As Integer, strOrig As String, today As Date

    today = Now()
    strOrig = ActiveCell
    oldLen = Len(strOrig)

    strOut = strOrig & Chr(10) & DateSerial(Year(today), Month(today), Day(today))

    ActiveCell = strOut

    With ActiveCell.Characters(Start:=1, Length:=oldLen).Font
    .Strikethrough = True
    End With

    With ActiveCell.Characters(Start:=oldLen + 1, Length:=Len(strOut) - oldLen).Font
    .Strikethrough = False
    End With

    End Sub
    </pre>


  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: macro to format part of a cell (2003)

    M Barron

    Thanks much for the macro.

    It didn't exactly do what I wanted so I tried to adapt it. No go.

    Let me try again as to what I need.

    Today is Oct 5. We show our dates as 10/5.

    As of today, we have a project due, say, on 11/5 as recorded in the current cell. But today, we learn that the delivery will be pushed out to 11/20. Selecting the cell, adding the alt+enter, and typing 11/20 is no big deal. Backing up while editing to select 11/5 is also no big deal. Now comes where I needed some help. With 11/5 selected, I want that to be formated with a strikethru.

    So the cell contents would look like
    11/5 <--struck thru
    11/20

    Now we go to Oct 20. We find out that the project has slipped again. Back into editing the cell, adding an alt+enter after 11/20, enter the new date of, say, 12/1, selecting 11/20, and striking it thru.

    So the cell contents now looks like
    11/5 <--struck thru
    11/20 <--struck thru
    12/1

    Of course, if the macro could do more, that would be great. However, I would see that the macro would have to prompt for a date, figure out where the old date was (usually at the end of the cell as of "now"), add the alt+enter, add the new date, and strike out the old date. Striking out the old date is not as simple as striking out the contents of the cell before adding the new date. Sometimes, there will be a word such as "Target" at the beginning of the cell, although not very often.

    PS I just took over responsibility of this workbook, so getting rid of non-date contents may not be simple to do in terms of retaining the value of that content; I'd have to check with the previous owner.

    Fred

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to format part of a cell (2003)

    Try this one then with one note. A valid date needs to be entered.:
    <pre>Sub strikeit()
    Dim strOut As String, oldLen As Integer, strOrig As String, today As Date, newDate As Date

    today = Now()
    strOrig = ActiveCell
    oldLen = Len(strOrig)

    newDate = Application.InputBox("New Date", "Enter New Date", DateSerial(Year(today), Month(today), Day(today)))


    strOut = strOrig & Chr(10) & DateSerial(Year(newDate), Month(newDate), Day(newDate))

    ActiveCell = strOut

    With ActiveCell.Characters(Start:=1, Length:=oldLen).Font
    .Strikethrough = True
    End With

    With ActiveCell.Characters(Start:=oldLen + 1, Length:=Len(strOut) - oldLen).Font
    .Strikethrough = False
    End With

    End Sub
    </pre>


  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: macro to format part of a cell (2003)

    M Barron,

    Very cool. But not quite there yet. A few things I changed so I think it's working as I want:
    - even if I enter a date like "11/6" w/o the quotes, the returned date is "11/6/2007". Since cell width is at a premium, we leave off the year digits. So I added a strDate as String and after your

    newDate = Application.InputBox("New Date", "Enter New Date", DateSerial(Year(today), Month(today), Day(today)))

    I added

    strDate = Format(newDate, "mm/dd")

    I also changed your statement
    strOut = strOrig & Chr(10) & DateSerial(Year(newDate), Month(newDate), Day(newDate))

    to
    strOut = strOrig & Chr(10) & strDate

    - speaking of the year digits, we understand that if I enter a date like 1/6 at this time of year, it means 1/6/08. However, the code is returning 1/6/2007. If the year digits were left off, then the reader would have to interpret the year based on context (if I'm in early 2007, "1/10" means "1/10/07" but if I'm around now it would mean "1/10/08"). This is not a problem. The above fix also took care of that since whatever the year digits are they're dropped anyway.

    And best of all, it works even when I'm starting with a totally blank cell.

    The only thing it doesn't do is to leave a word like "Target" alone. I think I can live with that.

    Thanks again.

    Fred

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to format part of a cell (2003)

    Now that you've included the fact that there is something else in the cell other than the date. This one will strike through evrything after the first space " ":
    <pre>Sub strikeit()
    Dim strOut As String, oldLen As Integer, strOrig As String, today As Date
    Dim strDate As String, firstSpace As Integer

    today = Now()
    strOrig = ActiveCell
    oldLen = Len(strOrig)
    If InStr(strOrig, " ") > 0 Then
    firstSpace = InStr(strOrig, " ")
    Else
    firstSpace = 0
    End If




    strDate = Application.InputBox("New Date", "Enter New Date", _
    Format(DateSerial(Year(today), Month(today), Day(today)), "mm/dd"))


    strOut = strOrig & Chr(10) & strDate

    ActiveCell = strOut

    With ActiveCell.Characters(Start:=firstSpace + 1, Length:=oldLen - firstSpace + 1).Font
    .Strikethrough = True
    End With

    With ActiveCell.Characters(Start:=oldLen + firstSpace + 1, Length:=firstSpace).Font
    .Strikethrough = False
    End With

    End Sub

    </pre>


  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: macro to format part of a cell (2003)

    Thanks for the update.

    I think I can do some more work on this now that you got me started.

    In particular, sometimes we know the current date is no longer valid but we don't know what the new date should be. Of course we could wait for a new date. But sometimes we just want to show the date as ????. I think I could put in an error function to allow that since your newDate which receives the input is dim'd as a Date. Further, if putting in ????, then we want to fill the cell as yellow to highlight the fact that we need a date.

    Fred

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to format part of a cell (2003)

    The newDate variable was no longer used. I redid the macro to incorporate what you had mentioned in your first response. strDate was being used as the variable for the new date which is actually a string.

    The new version will turn the cell yellow if the response is ????. The four question marks are the only thing that will turn it yellow.
    <pre>Sub strikeit()
    Dim strOut As String, oldLen As Integer, strOrig As String, today As Date
    Dim strDate As String, firstSpace As Integer

    today = Now()
    strOrig = ActiveCell
    oldLen = Len(strOrig)
    If InStr(strOrig, " ") > 0 Then
    firstSpace = InStr(strOrig, " ")
    Else
    firstSpace = 0
    End If

    strDate = Application.InputBox("Enter New date or ???? if date is unknown", "New Date", _
    Format(DateSerial(Year(today), Month(today), Day(today)), "mm/dd"))
    If strDate = "????" Then
    With Selection.Interior
    .ColorIndex = 6
    End With
    End If

    strOut = strOrig & Chr(10) & strDate

    ActiveCell = strOut

    With ActiveCell.Characters(Start:=firstSpace + 1, Length:=oldLen - firstSpace + 1).Font
    .Strikethrough = True
    End With

    With ActiveCell.Characters(Start:=oldLen + firstSpace + 1, Length:=firstSpace).Font
    .Strikethrough = False
    End With

    End Sub
    </pre>


  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: macro to format part of a cell (2003)

    Thanks again for the update.

    With your help, I actually took this beyond your last post. For example, the dates are when things are due and we try to deliver on time. However, sometimes an actual delivery date is so much different than the promised dated (and, yes, it can be much earlier than promised) that we also include the delivery date but put it in parens. So I incorporated a query to ask if the date entered was a completion date. If so, I do NOT strikethru the previous date (so I had to count how many characters were previously struck out before that), add the completion date under the last promised delivery date, and color the cell blue (for completed).

    The key thing in all of this was the "With ActiveCell.Characters(...." statement. I couldn't get that while recording a macro and I couldn't record a macro while editing a cell to strike out characters.

    I even have the macro entering the initial date for the cell and not striking out anything. The only thing that got me there was when I stored the contents in the ActiveCell, it took my string formatted as m/d and re-formatted it as "dd-mmm", which was totally undesirable. I was too lazy to figure out how to say the cell should just take the string as is (which I've since figured out), so, if the cell was previously empty, I just concatenated a space before and after as part of the strDate before storing back into the cell.

    Fred

Posting Permissions

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