MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Using excel VBA to change a cell color

  Asked By: Meenachi    Date: Aug 24    Category: MS Office    Views: 9367

My problem:

I have a spreadsheet in which I want to change the background
(interior) color of specific cells. I was using conditional
formatting, but now need more than three conditions.

I have a VBA function that calculates the value of a cell. Based on
that value I want to color the background. I have viewed, and used,
some of the samples in VB as well as some I found on-line. They all
seem to be selfcontained SUBS. When in the VB editor, I can click
on the sub and run it - it works fine.

However if I call that sub from my function - nothing (the function

Hopefully I am not trying to do something that can't be done and,
for mysake, I hope this is not too much of an elementary question.
If it is I'll be eating a lot of crow. Although it would be nice to
change its color to, say, white. :)

Ok, I probably went on too long.

Anyway, thanks for any advice regarding this problem of mine.



4 Answers Found

Answer #1    Answered By: Isaac Evans     Answered On: Aug 24

Here is the code that can help you.

Sub CompararCeldasLibros()
Dim numero As Double
numero = Round(ActiveCell.Value, 4)
'Debug.Print "Numero:" & numero
'Debug.Print "ActiveCell:" & Round(ActiveCell.Value, 4)
If Round(ActiveCell.Value, 4) = numero Then
ActiveCell.Interior.Color = RGB(255, 255, 0)
ActiveCell.Interior.Color = RGB(255, 0, 0)
End If
End Sub

Answer #2    Answered By: Erin Dunn     Answered On: Aug 24

Functions aren't meant to change  formatting etc. only return a value.

Have your code called from a Worksheet Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
'call your macro here (your macro can remain in a standard code
End Sub

This should be in the worksheet code module of the worksheet you want
this to happen in. Use Target instead of Activecell or Selection.

Answer #3    Answered By: Ana Bradley     Answered On: Aug 24

I was aware that some tasks
shouldn't be done in a Function but am not sure of which should and which
should not.

Answer #4    Answered By: Bernard Gutierrez     Answered On: Aug 24

Don't forget the you can have quite a complex formula (with and's
& OR's and < & >) in the conditional formatting box. I use this to
format font colors based  on value and have a formula so complex that
it is not all visable in the text box [the edit capability is really
awkward like this too). I actually exceeded the length for one and
had to use all three conditional boxes to get what I wanted. I have
something between 20 and 30 value limit tests.

Didn't find what you were looking for? Find more on Using excel VBA to change a cell color Or get search suggestion and latest updates.