activecell offset help

[aspence]

I have the code below, which defines a range in column K then searches that range for any cell where the value equals "Customer Tickets". From there, I am having trouble getting what I want out of the macro.

What I need is:
For every occurance of "Customer Tickets" in column K, i need some of the data in the corresponding rows to be changed to "Checked".

For example, if the macro finds cell K19 to be equal to "Customer Tickets, then I need the value in cells M19:O19 to all equal "Checked" and I cant seem to get the offset to work correctly.

Sub CustTick()
 
Dim metric As Range
' highlights variable range in column K
LastRow = Range("K1").End(xlDown).Row
Range(Cells(2, "K"), Cells(LastRow, "K")).Select
' sets selection as the range to search
Set metric = Selection
For Each Cell In metric
  If Cell = "Customer Tickets" Then
    ' need macro
  End If
Next Cell
End Sub

Any ideas?

 

[Scott Huish]

First off, it's very rare that you need to Select anything in VBA. You can work with ranges directly.

Sub test()
Dim c As Long, d As Range
c = Range("K" & Rows.Count).End(xlUp).Row
For Each d In Range("K1:K" & c)
    If UCase(d) = "CUSTOMER TICKETS" Then d.Offset(, 2).Resize(, 3) = "Checked"
Next
End Sub

 

[aspence]

d.Offset(, 2).Resize(, 3)

Is this a range selection?

If I needed this to do something other than change the cell value, like format the color to red instead of changing the cell value, will it do this with each cell in the range?

 

[Scott Huish]

It's specifying a range, but it's not actually Selecting it.

Sure specify what you want.

d.Offset(, 2).Resize(, 3).Interior.Color=vbRed

 

[aspence]

As you stated earlier

it's very rare that you need to Select anything in VBA. You can work with ranges directly.

Is it possible to take this new specified range and compare each cell in the range to another variable or cell? For example, if each of the cell in the range have values greater than the cell value of column L in the same row, then either change the cell value or format the color.

I really hope I am not overcomlicating things. Thanks for the help so far.

 

[Scott Huish]

Why not just use Conditional Formatting for that?

 

[aspence]

The form I am trying to modify currently uses conditional formatting. The problem is, there is so much repetative information in the workbook with a large amount of formatting, that when you attempt to filter data, the workbook freezes for extended periods of time and I am looking for alternatives to avoid that situation. When I clear the formatting from the workbook and modify the cells directly, the data filtering process runs very smoothly.

 

[rmscho02]

If I understand your issue, you want to go through several rows, and for each row if the value in Col K = "something" then change the values in Col M, N, O to something. Here's my recommendation:

lastRow = Range("A1000000").End(xlUp).Row 'I prefer xlUp instead of xlDown in case you have any blanks in that column, or just pick the column you know can't have blanks
startRow = 2

For r = startRow To lastRow
If Cells(r, "K").Value = "Customer Ticket" Then
Cells(r,"M").Value = whatevervalueyouwant
Cells(r, "N").Value = whatevervalueyouwant
Cells(r, "O").Value = whatevervalueyouwant
End If
Next r

 

[Scott Huish]

Do you want to individually change each cell that has a greater value than Column L, or only if they all do,and change them as a whole?

 

[aspence]

It would be an individual basis.