Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 26th, 2013, 04:06 PM
Registered User
 
Join Date: Feb 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Case Statement on Changed Cell's Value

Hello all,

While perusing the interwebs, I found the following sub below. The idea is that when a cell is changed in the worksheet that it runs a case statement against what the changed cell's new value is. It seems pretty straight forward but when I run it in debug mode the Set Rng1 = Range(Target.Address) is making Rng1 = the target's VALUE and not the CELL. So if I change V16 to J then Rng1 = J and the Case statement then apparently cycles through every cell in column J until it errors out. What I don't understand is that when I hover over Range(Target.Address) it shows Target.Address is $V$16 which is correct... So why is it setting Rng1 = "J" (the value)??

If Rng1 should equal "J" (what I change the cell to) and not "V16" (the cell that was changed), what should I use so that the Switch statement only cycles through the 1 changed cell instead of a whole column?

Please advise.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
     
    Dim Cell As Range
    Dim Rng1 As Range
     
    On Error Resume Next
    Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
    On Error GoTo 0
    If Rng1 Is Nothing Then
        Set Rng1 = Range(Target.Address)
    Else
        Set Rng1 = Union(Range(Target.Address), Rng1)
    End If
    For Each Cell In Range(Target.Address)
        Select Case Cell.Value
        Case vbNullString
            Cell.Interior.ColorIndex = xlNone
            Cell.Font.Bold = False
        Case "J", "B", "S"
            Cell.Interior.ColorIndex = 4
            Cell.Font.Bold = True
            Target.Value = UCase(Target.Value)
        Case Else
            Cell.Interior.ColorIndex = xlNone
            Cell.Font.Bold = False
        End Select
    Next
     
End Sub

Last edited by meissen; February 26th, 2013 at 04:10 PM..
 
Old March 3rd, 2013, 10:11 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

You can have a short code then

Code:
 
         Select Case Target.Value
        Case vbNullString
            Cell.Interior.ColorIndex = xlNone
            Cell.Font.Bold = False
        Case "J", "B", "S"
            Cell.Interior.ColorIndex = 4
            Cell.Font.Bold = True
            Target.Value = UCase(Target.Value)
        Case Else
            Cell.Interior.ColorIndex = xlNone
            Cell.Font.Bold = False
        End Select
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Case statement in Where mnp13 Oracle 5 March 8th, 2013 07:27 AM
How to use case in where statement Beginner_2012 SQL Language 2 February 2nd, 2012 09:19 PM
Help with Case Statement eastwest SQL Server 2005 8 August 6th, 2009 05:19 PM
case statement Hudson40 Access VBA 1 February 11th, 2005 11:31 AM
case statement jakeone Beginning PHP 10 August 19th, 2003 03:03 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.