JavaBlur
asked on
Detecting Lost Focus in a Cell?
Hi,
What I need here is by entering value A in Cell A and value B in Cell B, the excel sheet will be able to format cell C value given I write a macro for it. The problem is, can excel sheet detects the moment I have key in value in cell B, so that it runs the macro to auto generate the value in Cell C?
Pls help. thanks.
What I need here is by entering value A in Cell A and value B in Cell B, the excel sheet will be able to format cell C value given I write a macro for it. The problem is, can excel sheet detects the moment I have key in value in cell B, so that it runs the macro to auto generate the value in Cell C?
Pls help. thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yeah that can be done
the easiest way is to first declare a named range in your workbook that can be done through the menu
-insert | name | define
-and then use a range
-type the name of the range like > MyRange1
now in your code do something like
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("MyRange1")) Is Nothing Then
ActiveCell.Offset(0, 1).Value = "this is in C"
End If
End Sub
the intersect function checks for the target cell to be in the named range
let me know if you need some more
:O)Bruintje
the easiest way is to first declare a named range in your workbook that can be done through the menu
-insert | name | define
-and then use a range
-type the name of the range like > MyRange1
now in your code do something like
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("MyRange1")) Is Nothing Then
ActiveCell.Offset(0, 1).Value = "this is in C"
End If
End Sub
the intersect function checks for the target cell to be in the named range
let me know if you need some more
:O)Bruintje
ASKER
thank you sooo much.. wat if i need a few ranges, for eg. H19:H53, K19:K53, L19:L53?
you can do that also with the define name
just select all the parts in the sheet that you need to check on
with the ctrl button down and the mouse you can select multiple ranges
and then define the name for the selected ranges through the menu
just select all the parts in the sheet that you need to check on
with the ctrl button down and the mouse you can select multiple ranges
and then define the name for the selected ranges through the menu
ASKER
for the code you have given,
you use target.address = "$B$2",
how can i make the checking a bit more dynamic instead of just hard-coding it to $B$2? becos i need to do a looping to check a few rows.. thanks..
javablur