-
Jul 13th, 2011, 08:55 PM
#1
Thread Starter
Member
[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
-
Jul 13th, 2011, 10:44 PM
#2
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
-
Jul 14th, 2011, 08:36 PM
#3
Thread Starter
Member
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 )
-
Jul 14th, 2011, 08:50 PM
#4
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.
-
Jul 14th, 2011, 08:52 PM
#5
Thread Starter
Member
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 !
-
Jul 14th, 2011, 09:10 PM
#6
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.
-
Jul 14th, 2011, 09:12 PM
#7
Thread Starter
Member
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|