Using Offset xlRight

[Trevor G]

I am struggling with using the Offset xlRight to place some text in the next empty column.

Basically I am trying to go through a range of data and if a cell is coloured green I would like to place in some text like Found at the end of the activecell cell row + 1 cell to the right. I am using a userform with a combo box and in the Change event I have a case statement to colour cells if it meets a value of either 3 or 4. But my end result is to have the word Found to filter on then copy the filter to a Found Result sheet

Here is a copy of the Code I am using

Private Sub cbo1_Change()
Select Case Me.cbo1.Value
Case "Communication & Influencing Skills"
Range("E6").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 3 Or ActiveCell.Value = 4 Then
ActiveCell.Interior.Color = vbGreen
End If
ActiveCell.Offset(1, 0).Select
Loop
Case "Delivery Focus"
Range("F6").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 3 Or ActiveCell.Value = 4 Then
ActiveCell.Interior.Color = vbGreen
End If
ActiveCell.Offset(1, 0).Select
Loop
Case "Business Diagnosis"
Range("G6").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 3 Or ActiveCell.Value = 4 Then
ActiveCell.Interior.Color = vbGreen
End If
ActiveCell.Offset(1, 0).Select
Loop
Case "Commercial Focus"
Range("H6").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 3 Or ActiveCell.Value = 4 Then
ActiveCell.Interior.Color = vbGreen
End If
ActiveCell.Offset(1, 0).Select
Loop
Case "Collaboration"
Range("I6").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 3 Or ActiveCell.Value = 4 Then
ActiveCell.Interior.Color = vbGreen
End If
ActiveCell.Offset(1, 0).Select
Loop
Case "Personal Leadership"
Range("J6").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 3 Or ActiveCell.Value = 4 Then
ActiveCell.Interior.Color = vbGreen
End If
ActiveCell.Offset(1, 0).Select
Loop


End Select



End Sub

 

[Norie]

Trevor

The only offset I can see is one row down, not 1 column to the right.

For that you need Offset(0,1), ie Offset(row, column).

 

[Trevor G]

Hi Norie,

Yes I am aware of moving down the column, but what I am aiming for that when I find a result and it colours the background at the same time in Column BO on the activerow it adds in the text Found.

I will then beable to use a Filter to copy all the Found data to a result sheet.

So my struggle is trying to work out either xlRight or use the current row and then Column BO Cell of that row to add in the text Found.

I hope that helps with my query.

 

[Trevor G]

I have the solution thanks for reading my thread.

I am using activecell.offset

here is sample

ActiveCell.Offset(0, 62).Value = "Found"

 

[Norie]

You could use the Offset I suggested when you do the formatting.

ActiveCell.Offset(0,1) ="Found"

That's if it is one column to the right of the column the cell you are formatting is in.

If BO is a set column then try this.

Cells(ActiveCell.Row, "BO").Value = Found

Or does BO actually depend on which column you are looking in?

 

[Trevor G]

Thanks Norie,

I like the second option you have suggested as the way I was going to use would be a lot of changing the numbers in the Offset.

Hope you have a great weekend.

 

[Norie]

I'll post this anyway then, didn't get a chance to test it though.

Option Explicit

Private Sub cbo1_Change()
Dim rng As Range
    Select Case Me.cbo1.Value
        Case "Communication & Influencing Skills"
            Set rng = Range("E6")
        Case "Delivery Focus"
            Set rng = Range("F6")
        Case "Business Diagnosis"
            Set rng = Range("G6")
        Case "Commercial Focus"
            Set rng = Range("H6")
        Case "Collaboration"
            Set rng = Range("I6")
        Case "Personal Leadership"
            Set rng = Range("J6")
    End Select
    Do Until rng.Value = ""
        If rng.Value = 3 Or rng.Value = 4 Then
            rng.Interior.Color = vbGreen
            Cells(rng.Row, "BO").Value = "Found"

        End If
        Set rng = rng.Offset(1, 0)
    Loop
End Sub