Thread: Select the next column and start on line 4 when a condition is satisfied.

Hi there,

I would like Excel to select the next col starting on row 4 when the target.row hits line 11. I have the following code so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("2:2").Interior.ColorIndex = xlNone
    Range("A:A").Interior.ColorIndex = xlNone
    Range("A" & Target.Row).Interior.ColorIndex = 23
    Cells(2, Target.Column).Interior.ColorIndex = 23
    
    If Cells(Target.Row) = 11 Then
    Cells(Target.Row - 7, 1).Select
    End If
   
End Sub

While I'm getting no error, it doesn't actually go to the next col and start on row 4.

Any ideas?

nice, thanks!

By the way, what does this line of code do?

Cells.Interior.ColorIndex = xlNone

I see.

I was exploring more on how to best go about entering numbers into the sheet with enhanced visualization.

One of the things I would like to do is to make the font size of the target cell bigger when selected, and you when you move to the next cell, the font size goes back to its original font size.

How much code change does this involve?

Currently I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Cells.Interior.ColorIndex = xlNone
    Cells(2, Target.Column).Interior.ColorIndex = 27
    Cells(Target.Row, 1).Interior.ColorIndex = 26
    
    With ActiveCell
    .Interior.ColorIndex = 43
    .Font.Size = 14
    End With
    
    If Target.Row > 11 Then
    Cells(4, Target.Column + 1).Select
    Cells(2, Target.Column + 1).Interior.ColorIndex = 27
    End If

End Sub

THanks

wow nice.

How do I add a numeric check ONLY on range B4 to P10?

I know how to do it if the format is applied to the entire column, but what if i only want to add a check on range B4 to P10?

So...say if a alphabetic letter is put in instead of a number, it erases the value and prompts users to re-enter a numeric value.

Any ideas?

Any one able to assist further on this?

Hey, it's not exactly working, but the syntax is correct though..

   
    Cells.Interior.ColorIndex = xlNone
    Cells.Font.Size = 14
    Cells(2, Target.Column).Interior.ColorIndex = 27
    Cells(Target.Row, 1).Interior.ColorIndex = 26
    
    With Target
        .Interior.ColorIndex = 43
        .Font.Size = 18
    End With
    
    If Target.Row > 4 And Target.Row < 11 Then
    If Not IsNumeric(Target) Then
        Target.ClearContents
        MsgBox "this is not number"
    End If
    End If
    
    If Target.Row > 11 Then
    Cells(4, Target.Column + 1).Select
    Cells(2, Target.Column + 1).Interior.ColorIndex = 27
    End If

That line you added seems to suggest to limit the range to be checked.

When I copied over your code, I only copied the line with the range check but not the entire code, which is why I missed that line.

However, I keep thinking that this numeric check should be in the worksheet_change event, so the code below shows how it is laid out, but it is not working. I wonder if it is because I put it in the worksheet_change event instead of worksheet_selectionchange...

hmm

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Row > 4 And Target.Row < 11 Then
    If Not IsNumeric(Target) Then
        Target.ClearContents
        MsgBox "this is not a number!"
    End If
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Cells.Interior.ColorIndex = xlNone
    Cells.Font.Size = 14
    Cells(2, Target.Column).Interior.ColorIndex = 27
    Cells(Target.Row, 1).Interior.ColorIndex = 26
    
    If Target.Count = 1 Then
    With Target
        .Interior.ColorIndex = 43
        .Font.Size = 18
    End With
    End If
    
    If Target.Row > 11 Then
    Cells(4, Target.Column + 1).Select
    Cells(2, Target.Column + 1).Interior.ColorIndex = 27
    End If
    
End Sub