Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 0 Times in 0 Posts
    Hi there,

    I am using excel to set up a simple program for some activities I am doing over the next month or so (see attached). I have created the code below (with the assistance of previous threads, help sites) to aid the shading of cells and font using the case function - the case functions as presented below. I still have two problems that I can live with, but would really like to avoid if I can:
    - If I delete contents or cut an paste values I get an error "'run time error type 13 - TYpe mismatch'
    - is there a way to leave a cell color shading as it was, ie the shading was manually formatted (not via the case function)

    I am a VBA gumby and rely on other people's assistance. Once again, it would be great if someone out there can help me out.

    The Code is located in the worksheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer
    If Not Intersect(Target, Range("C3:AZ50")) Is Nothing Then
    Select Case Target
    Case "N/A"
    icolor = 3
    Target.Font.ColorIndex = 3
    Case "A"
    icolor = 4
    Target.Font.ColorIndex = 4
    Case "WE"
    icolor = 15
    Target.Font.ColorIndex = 15
    Case "PH"
    icolor = 18
    Target.Font.ColorIndex = 18
    Case "B"
    icolor = 1
    Target.Font.ColorIndex = 1
    Case "?"
    icolor = 27
    Case "T"
    Target.Font.ColorIndex = 26
    icolor = 26
    Case "Maint"
    icolor = 3
    End Select
    Target.Interior.ColorIndex = icolor
    'Is there a way to leave the shading that was already in the cell as it was rather than being reset?
    'When I drag cells to fill (with contents not matched to the case conditions above) or clear cells in row 3 to7, I get an 'run time error type 13 - TYpe mismatch'. Is there a way of avoiding this?
    End If
    End Sub

    The 'Refresh' code I have in my macros (I use this if I have made a number of changes and this is the macro that modifies my 'manual shading', cells I3:I4).

    Public Sub Refresh()
    For Each C In Worksheets("Program").Range("C3:AA150")
    C.Value = C.Value
    Next C
    End Sub
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Jun 2002
    Georgia, U. S. A.
    Thanked 0 Times in 0 Posts
    This is a first for me. I've asked several questions but never supplied a thought for an answer before. I hope I'm correct when I say, if you cut and paste, you are typically pasting the format of the cell as well as the contents. You could right click when you paste and choose 'paste special' from the menu. Then if you choose 'values' it should not change the formatting.
    In your case though, it looks as if the formatting is based on the content of the cell so my comments above probably won't be helpful.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    Target is not always 1 cell which your code presumes. You get the error when the Target is a range of cells (which would happen when you copy more than 1 cell). You need to loop through all the cells in the intersection of Target and the range to run the code.

    To avoid resetting the manual changing, you will need a way to distinguish the formatting of the manually changed cells. Is there something the macro can key on (contents, location, manually selected color?)


Posting Permissions

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