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
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 Display Modes
  #1 (permalink)  
Old September 29th, 2004, 11:44 PM
Friend of Wrox
 
Join Date: Sep 2004
Location: New Delhi, Dekhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anantsharma Send a message via Yahoo to Anantsharma
Default Fill color in Excel Cell

HI,

I am populating an Excel sheet fetching the data from SQL Server. For some special criteria, I want to Fill the cell with a particular colur say "Blue".

I could find any methd to fill a cell with colour. Please let me have the idea how to do that.

Thanks

B. Anant
__________________
B. Anant
Reply With Quote
  #2 (permalink)  
Old September 30th, 2004, 03:56 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use the Interior object, eg.

Code:
Selection.Interior.ColorIndex=5
Selection.Interior.Pattern=xlSolid
will set the current selection to blue

Also, you can play with the Font object if you need to change the colour of the text in a cell, eg.

Code:
Selection.Font.ColorIndex=5
or to set the first 3 characters of the active cell to blue would be

Code:
ActiveCell.Characters(1, 3).Font.ColorIndex=5
Reply With Quote
  #3 (permalink)  
Old October 5th, 2004, 04:31 AM
Authorized User
 
Join Date: Jun 2004
Location: Sandown, Isle of Wight, United Kingdom.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

When you come across a problem such as this a good way around it is to record a macro.

Go to a separare workbook and record a macro, select the cell and format the cell with fill colour, borders etc as required, hen stop the recording.

View the code that the recording has creating and cut and paste the relevent bit into your project.

Reply With Quote
  #4 (permalink)  
Old October 25th, 2004, 04:33 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

I have a similiar task/problem to solve.

I want to be able to select an entire column (A1 for example) and be able to check every value in a that column, if its a certain value, then change its background color.

In C syntax, it would be like this i guess:

foreach(cell in column) { if (value(cell) < 5) bgcolor(cell,blue) }

I just guess VBA is too high level for me :)

Any suggestions? Preferebly in VBA, it'd be handy to learn...

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

You need to do something like

Code:
  Dim oCell As Excel.Range
  Dim oSheet As Excel.Worksheet

  Set oSheet = ActiveSheet

  For Each oCell In oSheet.Range("A:A").SpecialCells(xlCellTypeConstants)
    Select Case CStr(oCell.Value)
    Case "3", "three"
      With oCell.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
      End With
    End Select
  Next


This should go through the current worksheet's cells in Column A, processing only those that have some sort of value. In my example, if it meets a numeric value of 3, or text value of "three", it will set the cell's background to green.
Reply With Quote
  #6 (permalink)  
Old October 25th, 2004, 04:57 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

As luck has it, as soon as i posted that, i found a solution 2 minutes later (been searching for something on this for a couple of days), heres an even simpler solution:

Sub DoOnSelection()
Dim oCell As Range
For Each oCell In Selection
  If oCell.Value < 5 Then
  oCell.Interior.ColorIndex = 3
  End If

Next
End Sub

which does almsot exactly as my C-syntax PDL styled code does.

Next question, how do i do Cell.Value = "*shoes*"

where that would return

"too many shoes"
"shoes galore"
"some shoes are too bigbarr"

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

If you're sure it's a string value:

Code:
  If myCell.Value Like "*shoes*" Then
    'do something
  End If


or

Code:
  If InStr(1, UCase$(myCell.value), "SHOES", vbTextCompare) Then
    'do something
  End If
Reply With Quote
  #8 (permalink)  
Old October 25th, 2004, 06:08 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

Thanks for those. I can't quite get the IF... LIKE thing to work though for some reason. I'm actually trying to check if the value is NOT like a certain value, let me explain...

a column, with values like "RECOVERY, "RECOVERY OS", "WIN2K_REC","NA","PRODUCTION"

i want to pick out the cells that aren't recovery (or like recovery, such as the win2k_rec one), and highlight them with the aforementioned method.

I assumed this would work...
If InStr(1, UCase$(myCell.value), "REC", vbTextCompare) Then ... End If


But that doesnt seem to work, and yes, it needs to be case insensitive

Reply With Quote
  #9 (permalink)  
Old October 25th, 2004, 06:09 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

Correction....

I assumed this would work...
If Not InStr(1, UCase$(myCell.value), "REC", vbTextCompare) Then ... End If



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

InStr returns an integer, indicating where in the string a match was found.

Therefore:

Code:
  If InStr(1, UCase$(myCell.Value), "RECOVERY", vbTextCompare) = 0 Then
    '...
  End If


is the construct you need. A return value of zero means the string "RECOVERY" was not found in myCell.Value
Reply With Quote
Reply


Thread Tools
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 03:25 AM
Filling a Oval in a Excel Cell with Color sinajs Excel VBA 0 January 22nd, 2007 12: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 10:04 AM.


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