Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old October 25th, 2004, 06:25 AM
Authorized User
 
Join Date: Oct 2004
Location: Edinburgh, Scotland, United Kingdom.
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to SiliconFuRy
Default

Ah, i understand now.

Sort of works, but only on the first cell (it highlights it because its a column header that doesnt contain the string "recovery")

the loop seems to bail out after the first cell... heres the complete code so far... any thoughts?

Sub HighlightCVolume()
  Dim oCell As Excel.Range
  Dim oSheet As Excel.Worksheet

  Set oSheet = ActiveSheet

  For Each oCell In oSheet.Range("F:F").SpecialCells(xlCellTypeConstan ts)
    If InStr(1, UCase$(oCell.Value), "REC", vbTextCompare) = 0 Then
    With oCell.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
      End With
    End If
  Next
End Sub

Reply With Quote
  #12 (permalink)  
Old October 25th, 2004, 06:34 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry - your code works fine for me. I gave it a go with a worksheet whose cells in F1 thru F5 were populated

RECOVERY
RECOVERY OS
WIN2K_REC
NA
PRODUCTION

It highlighted the last two cells (NA and PRODUCTION). Isn't that what you wanted??
Reply With Quote
  #13 (permalink)  
Old October 25th, 2004, 06:36 AM
Authorized User
 
Join Date: Oct 2004
Location: Edinburgh, Scotland, United Kingdom.
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to SiliconFuRy
Default

Ah, i forgot, all the items in that columns are results from a Vlookup... is it possible to check these values (returned from the vlookup)?

Reply With Quote
  #14 (permalink)  
Old October 25th, 2004, 07:04 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Maybe do something like

Code:
Sub HighlightCVolume()
  Dim oCell As Excel.Range
  Dim oSheet As Excel.Worksheet

  Set oSheet = ActiveSheet

  For Each oCell In oSheet.Range("F:F").SpecialCells(xlCellTypeFormulas)
    On Error Resume Next
    If InStr(1, UCase$(oCell.Value), "REC", vbTextCompare) = 0 Then
      With oCell.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
      End With
    End If
  Next
End Sub
A good starting point is to read the online help for the functions you're using. Click on SpecialCells and hit F1
Reply With Quote
  #15 (permalink)  
Old October 25th, 2004, 07:11 AM
Authorized User
 
Join Date: Oct 2004
Location: Edinburgh, Scotland, United Kingdom.
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to SiliconFuRy
Default

Ah, excellent, that works fine. Thanks.

I've been trying to get info from the MSDN website (worked a treat when doing development in C#), but I'm finding it a right bitch finding info in the same way for office development (we're using office 2000 here).

Also forgot about the "hitting F1 while cursor is on a keyword" thing.

Thanks again, this has shaved a few days off development for this report :D

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to get Excel Cell color populate .NET Framework 2.0 2 March 3rd, 2014 04:25 AM
Filling a Oval in a Excel Cell with Color sinajs Excel VBA 0 January 22nd, 2007 01:47 PM
change cell color martin1 Visual Basic 2005 Basics 2 October 4th, 2006 08:55 AM
text box to fill cell completely(no white border a Raif ASP.NET 1.0 and 1.1 Professional 0 August 20th, 2004 03:25 PM



All times are GMT -4. The time now is 07:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.