Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colour specific cell based on 2 other cell values
Hi, I've got spreadsheet with a list of clients, each client having
their own row. I have recorded a macro which goes through each client and fills in colour on the Q column if they have a "Yes" on either column V or F. The clients are numbered on column A (I use the last client to determine how far down to go). The problem I have is that my macro is a bit too slow for my liking. Any ideas on an improvement? Dim clients As Variant Dim distance As Variant Range("A2").Select Selection.End(xlDown).Select distance = 0 clients = ActiveCell Range("F3").Select Do Until distance = clients If ActiveCell = "Yes" Then ActiveCell.Offset(0, 11).Range("A1").Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ActiveCell.Offset(1, -11).Range("A1").Select Else ActiveCell.Offset(0, 16).Range("A1").Select If ActiveCell = "Yes" Then ActiveCell.Offset(0, -5).Range("A1").Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ActiveCell.Offset(1, -11).Range("A1").Select Else ActiveCell.Offset(1, -16).Range("A1").Select End If End If distance = distance + 1 Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colour specific cell based on 2 other cell values
That's great, thanks for that Martin, much faster.
Once I get my head around how and what you have used there, i'm sure I can speed up other macros Thanks Tony Martin Fishlock wrote: Tony Try this I have taken out all the selects and used a counter. Sub clients() Dim lLastRow As Long, lFirstRow As Long, lRow As Long Application.ScreenUpdating = False lLastRow = Range("A2").End(xlDown).Row lFirstRow = 3 For lRow = lFirstRow To lLastRow If Cells(lRow, 6) = "Yes" Or Cells(lRow, 22) = "Yes" Then With Cells(lRow, 17).Interior .ColorIndex = 4 .Pattern = xlSolid End With End If Next lRow Application.ScreenUpdating = True End Sub -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "bony_tony" wrote: Hi, I've got spreadsheet with a list of clients, each client having their own row. I have recorded a macro which goes through each client and fills in colour on the Q column if they have a "Yes" on either column V or F. The clients are numbered on column A (I use the last client to determine how far down to go). The problem I have is that my macro is a bit too slow for my liking. Any ideas on an improvement? Dim clients As Variant Dim distance As Variant Range("A2").Select Selection.End(xlDown).Select distance = 0 clients = ActiveCell Range("F3").Select Do Until distance = clients If ActiveCell = "Yes" Then ActiveCell.Offset(0, 11).Range("A1").Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ActiveCell.Offset(1, -11).Range("A1").Select Else ActiveCell.Offset(0, 16).Range("A1").Select If ActiveCell = "Yes" Then ActiveCell.Offset(0, -5).Range("A1").Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ActiveCell.Offset(1, -11).Range("A1").Select Else ActiveCell.Offset(1, -16).Range("A1").Select End If End If distance = distance + 1 Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell vlue based on cell back colour | Excel Worksheet Functions | |||
Fill cell colour based on input in another cell | Excel Worksheet Functions | |||
Default colour in cell based on specific entry | Excel Worksheet Functions | |||
change current cell colour based on the value of adjacent cell on other worksheet | Excel Programming | |||
Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown | Excel Programming |