UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Selecting Cells With Formatting Using Vba, Office 2013    
 
   
bazza
post Jul 8 2019, 12:10 PM
Post#1



Posts: 355
Joined: 13-February 03



Hi,

I have stitched together some code using some resources online and what I know myself. I am basically selecting cells within a range that have the same colour as a given target cell.

My problem is however my final selection keeps including the target cell. Is the a way I can omit the target cell from the final selection? I know its happening because I'm using the union function but I just cant find a way to omit it.

Code is here:

CODE

Dim objRange As Range, TargetCell As Range, i As Integer
  Set TargetCell = Range("BH3")
    For Each objRange In Selection
      If objRange.Interior.Color = Range("BH3").Interior.Color Then
        Set TargetCell = Union(TargetCell, objRange)
      End If
    Next objRange
  TargetCell.Select

Thanks
This post has been edited by bazza: Jul 8 2019, 12:12 PM
Go to the top of the page
 
Doug Steele
post Jul 8 2019, 01:14 PM
Post#2


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


See whether this works:

CODE
Dim objRange As Range, TargetCell As Range, i As Integer
Dim booFound As Boolean  

    For Each objRange In Selection
      If objRange.Interior.Color = Range("BH3").Interior.Color Then
        If booFound = False Then
           Set TargetCell = objRange
           booFound = True
        Else
           Set TargetCell = Union(TargetCell, objRange)
        End If
      End If
    Next objRange
  TargetCell.Select

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
bazza
post Jul 9 2019, 03:37 AM
Post#3



Posts: 355
Joined: 13-February 03



That works as I need it to, many thanks!

Out of interest where was I going wrong? I've never used boolean before so unsure of its capabilities/use.
Go to the top of the page
 
Doug Steele
post Jul 9 2019, 04:48 AM
Post#4


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Can't say you really did anything wrong, but since you'd initialized TargetCell to BH3, and were then adding additional cells to TargetCell, that meant BH3 would always be in the results. What I did was initialize TargetCell to the first cell found, then use the Boolean to indicate that a cell had been found. If it had, then subsequently found cells would continue to be added.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
bazza
post Jul 9 2019, 06:17 AM
Post#5



Posts: 355
Joined: 13-February 03



That makes sense.

What if my selection does not contain any cells which match the target though?

At the moment that scenario gives me an error.
Go to the top of the page
 
Doug Steele
post Jul 9 2019, 05:50 PM
Post#6


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Check the value of booFound

CODE
Dim objRange As Range, TargetCell As Range, i As Integer
Dim booFound As Boolean  

    For Each objRange In Selection
      If objRange.Interior.Color = Range("BH3").Interior.Color Then
        If booFound = False Then
           Set TargetCell = objRange
           booFound = True
        Else
           Set TargetCell = Union(TargetCell, objRange)
        End If
      End If
    Next objRange
  
    If booFound = True Then
      TargetCell.Select
    Else
      MsgBox "No matching cells found"
    End If

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 12:08 AM