+ Reply to Thread
Results 1 to 4 of 4

Macro to change colour of rows

  1. #1
    Registered User
    Join Date
    12-04-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    41

    Macro to change colour of rows

    Good morning,

    Please see the attached file, I have a macro running which changes the colour of the rows depending on the value in column H. It all appears to be working fine, however if, for example, I select BH in column H and try to drag this down i get an error message asking me to debug?

    I need to be able to drag down the initials to save a lot of time, can anyone assist?

    (Note- I have tried to attach the file however it is too large, I am getting run time error 13- 'type mismatch' appearing and it takes me to visual basic asking me to debug. Please see my macro below:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iColour As Integer
    If Not Intersect(Target, Range("H13:H2000")) Is Nothing Then
    Select Case StrConv(Target, vbUpperCase)

    Case Is = "CJ"
    iColour = 39
    Case Is = "OJ"
    iColour = 40
    Case Is = "BH"
    iColour = 38
    Case Is = "EB"
    iColour = 37
    Case Is = ""
    iColour = 34
    Case Is = "JS"
    iColour = 35

    End Select

    Target.EntireRow.Interior.ColorIndex = iColour
    End If

    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Last edited by bhodo; 05-13-2010 at 03:36 AM.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Macro to change colour of rows

    Hi bhodo,
    Can you please add code tags to you code as per the Forum rules?
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    12-04-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Macro to change colour of rows

    Hi Pike,

    My appolagies, I am not quite sure what you are asking for here

  4. #4
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Macro to change colour of rows

    dude
    Go back to your first post and click edit
    then

    Always add code tags to your VBA script [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

    the result will be

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1