Fill color in Excel Cell

Anantsharma · September 29th, 2004, 11:44 PM

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