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 September 29th, 2004, 11:44 PM
Friend of Wrox
 
Join Date: Sep 2004
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
 
Old September 30th, 2004, 03:56 AM
Authorized User
 
Join Date: Jun 2003
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
 
Old October 5th, 2004, 04:31 AM
Authorized User
 
Join Date: Jun 2004
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.

 
Old October 25th, 2004, 04:33 AM
Authorized User
 
Join Date: Oct 2004
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...

 
Old October 25th, 2004, 04:53 AM
Authorized User
 
Join Date: Jun 2003
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.
 
Old October 25th, 2004, 04:57 AM
Authorized User
 
Join Date: Oct 2004
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"

 
Old October 25th, 2004, 05:09 AM
Authorized User
 
Join Date: Jun 2003
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
 
Old October 25th, 2004, 06:08 AM
Authorized User
 
Join Date: Oct 2004
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

 
Old October 25th, 2004, 06:09 AM
Authorized User
 
Join Date: Oct 2004
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



 
Old October 25th, 2004, 06:20 AM
Authorized User
 
Join Date: Jun 2003
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





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





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