Results 1 to 7 of 7

Thread: [RESOLVED] [Excel VBA] Help...How to limit how many cells in a row change color

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2009
    Location
    Southern England
    Posts
    56

    Resolved [RESOLVED] [Excel VBA] Help...How to limit how many cells in a row change color

    MS Excel 2007. Have written the below code (for a pet work project) which makes cells in a row show as a certain color depending on what is entered in colum M. trouble is, it changes the whole of the row (from A to infinity), is there any way to limit it to say just the first 15 colums only?

    I'm pretty new to this and this is just something I am trying to do to pass time in a hotel room!!


    Private Sub Worksheet_Change(ByVal Target As Range)
    Call change
    End Sub


    Private Sub change()
    Dim name As String
    Dim col As Integer
    Dim i As Integer

    'Change the below line to the name of the worksheet this is to work on.
    name = "Global Asset List"
    'col is the column the code works on, i.e. you change something in column
    '5 and it won't change the colour, (NB, with col, A=1, B=2 etc...)
    col = 13 'This is colomn M

    'DO NOT CHANGE CODE BELOW
    For i = 1 To 150
    If (UCase(Worksheets(name).Cells(i, col).Value) = "EH") Then
    Worksheets(name).Rows(i).Font.Color = RGB(0, 0, 0)
    Worksheets(name).Rows(i).Interior.Color = RGB(0, 255, 0)
    End If
    If (UCase(Worksheets(name).Cells(i, col).Value) = "USA") Then
    Worksheets(name).Rows(i).Font.Color = RGB(0, 0, 0)
    Worksheets(name).Rows(i).Interior.Color = RGB(0, 0, 255)
    End If
    If (UCase(Worksheets(name).Cells(i, col).Value) = "CAN") Then
    Worksheets(name).Rows(i).Font.Color = RGB(0, 0, 0)
    Worksheets(name).Rows(i).Interior.Color = RGB(255, 0, 0)
    End If
    If (UCase(Worksheets(name).Cells(i, col).Value) = "SAM") Then
    Worksheets(name).Rows(i).Font.Color = RGB(0, 0, 0)
    Worksheets(name).Rows(i).Interior.Color = RGB(75, 176, 123)
    End If
    If (UCase(Worksheets(name).Cells(i, col).Value) = "") Then
    Worksheets(name).Rows(i).Font.Color = RGB(0, 0, 0)
    Worksheets(name).Rows(i).Interior.Color = RGB(255, 255, 255)
    End If
    Next i
    End Sub
    Last edited by ndtsteve; Jul 13th, 2011 at 09:25 PM. Reason: Change of title

  2. #2
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: [Excel VBA] Help...How to limit how many cells in a row change color

    just like this...
    Code:
    Worksheets(Name).Range("a1:o1").Font.Color = RGB(0, 0, 0)
    Worksheets(Name).Range("a1:o1").Interior.Color = RGB(255, 0, 0)
    suggestion: using select case statement is easier than If statement
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2009
    Location
    Southern England
    Posts
    56

    Re: [Excel VBA] Help...How to limit how many cells in a row change color

    Thanks Seenu.........but this doesn't appear to do what I wanted....maybe its just my understanding?

    If for example I enter USA in Cell M9 then the whole of row 9 goes blue ( from A to infinity)

    What I want is for only the first 15 cells (A9 to 09) to go blue

    This needs to apply for all the options ( EH USA CAN SAM )

  4. #4
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: [Excel VBA] Help...How to limit how many cells in a row change color

    i just shown u a sample how to limit the range of cells, do u expect the complete code sample? if yes i wil provide a sample code.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  5. #5

    Thread Starter
    Member
    Join Date
    Aug 2009
    Location
    Southern England
    Posts
    56

    Re: [Excel VBA] Help...How to limit how many cells in a row change color

    Yes please Seenu, just the code for one version.

    Have tried with what you sent befor but couldn't make it do what I wanted.....I am very new to this VBA and only doing it with the help of forums and office help pages !

  6. #6
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: [Excel VBA] Help...How to limit how many cells in a row change color

    try like this...
    Code:
    Private Sub CommandButton1_Click()
    Dim LastRow As Integer, i As Integer
    LastRow = Cells(Rows.Count, "M").End(xlUp).Row
    For i = 1 To LastRow
        With Range("A" & i & ":O" & i)
            Select Case UCase(Cells(i, "M"))
                Case "EH"
                    .Interior.Color = RGB(0, 255, 0)
                Case "USA"
                    .Interior.Color = RGB(0, 0, 255)
                Case "CAN"
                    .Interior.Color = RGB(255, 0, 0)
                Case "SAM"
                    .Interior.Color = RGB(75, 176, 123)
                Case ""
                    .Interior.ColorIndex = xlNone
            End Select
        End With
    Next
    End Sub
    the lastrow finds the last used cell in column M, if u want u can change to any number as u need.
    Last edited by seenu_1st; Jul 14th, 2011 at 09:13 PM.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  7. #7

    Thread Starter
    Member
    Join Date
    Aug 2009
    Location
    Southern England
    Posts
    56

    Re: [Excel VBA] Help...How to limit how many cells in a row change color

    Thanks a lot Seenu, you're a Star !!

Posting Permissions

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



Click Here to Expand Forum to Full Width