p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Excel VBA (http://p2p.wrox.com/forumdisplay.php?f=79)
-   -   Fill color in Excel Cell (http://p2p.wrox.com/showthread.php?t=19179)

Anantsharma September 29th, 2004 11:44 PM

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

gcianfanelli September 30th, 2004 03:56 AM

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

Martin Outlaw October 5th, 2004 04:31 AM

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.


SiliconFuRy October 25th, 2004 04:33 AM

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...


gcianfanelli October 25th, 2004 04:53 AM

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.

SiliconFuRy October 25th, 2004 04:57 AM

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"


gcianfanelli October 25th, 2004 05:09 AM

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


SiliconFuRy October 25th, 2004 06:08 AM

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


SiliconFuRy October 25th, 2004 06:09 AM

Correction....

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




gcianfanelli October 25th, 2004 06:20 AM

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


All times are GMT -4. The time now is 11:53 AM.

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