cursor and formatting

[ahhhmed]

I formatted the cells in C conditionally according to the values in A. When the values in A change, the color of C cells change.


I want the cursor to jump over the formatted rows.

any help is appreciated.

 

[Hui]

You can either Hide Column C

or

Group the Column and temporarily hide Column C using the outline controls

 

[ahhhmed]

Good idea, Hui.


But the problem is that the line formatting is always changing accrding to the value in A.


So every time there is that value in a cell in A, the line is formatted and the cursor has to jump over it.

 

[Luke M]

Right click on sheet tab, view code, paste this in and modify as needed:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

Dim CriteriaValue As Variant


'What is the criteria in col A?

CriteriaValue = 5


If Cells(Target.Row, 1).Value = CriteriaValue Then

Target.Offset(1, 0).Select

End If

End Sub

 

[ahhhmed]

Thanks a lot Luke M, That was very brilliant.

Now When I use the arrows I can move the cursor down, but I can't move it up

Why

 

[Luke M]

Can't move up at all, or can't move up to a row that is formatted? If the latter, I'm afraid the event macro doesn't know which direction you were going when you selected the "formatted row", and it's hardcoded to go down (controlled by the Target.Offset(1,0).Select )

An imperfect solution, admittedly. Another idea would be to have a worksheet_change event macro cycle through all the rows hiding/unhiding the ones of concern (borrowing from Hui's idea)

 

[ahhhmed]

OK

Is there any idea about how to automatically hide conditionally formatted rows?

 

[Luke M]

Ok, get rid of earlier event macro, put this one in:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TargetValue As Variant

TargetValue = 5


Application.ScreenUpdating = False

With Worksheets(1).Range("a1:a500")

.EntireRow.Hidden = False

Set c = .Find(TargetValue, LookIn:=xlValues)

If Not c Is Nothing Then

firstAddress = c.Address

Do

c.EntireRow.Hidden = True

Set c = .FindNext(c)

Loop While Not c Is Nothing

End If

End With

End Sub

 

[ahhhmed]

Thanks Luke, this is very kind of you.