MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

vba code help

  Asked By: Anita    Date: Feb 25    Category: MS Office    Views: 1190

I have four number combinations in cells of column Q. Based on the
value of the number I want the corresponding text in column a cells to
change color. For example if q2 = 5 then the text in a2 = needs to be
blue. I need a vba code to scroll down the length of column q and A
as the entries change.

here are my details:

q2=5 = blue in text A2
q2=4 = green in text A2
q2= -2 = red in text a2
q2 = 1 = pink text in a2

The values will change periodically.



3 Answers Found

Answer #1    Answered By: Eric Foster     Answered On: Feb 25

Search the Google Groups for "conditional formatting" vba, here:

Let us know how you go with your code.

Answer #2    Answered By: Oliver Evans     Answered On: Feb 25

Here is a site - He has a download file for conditional formating -
You could have almost unlimited conditions.

Have it set up in a sheet and it works GREAT.


He has done a nice job here.

Answer #3    Answered By: Geb Chalthoum     Answered On: Feb 25

Something like this will work...

Option Explicit

Sub Conditional_Shading()
Dim cl As Range
Dim RNG As Range
'get the last used cell of column  q
Set RNG = Columns("q").Find(What:="*", _
SearchDirection:=xlPrevious, _
For Each cl In Range([Q1], [RNG])
If cl > 0 And cl <= 5 Then
With Cells(cl.Row, 1).Interior
Cells(cl.Row, 1).Select
Select Case cl.Value
Case Is = 5
.ColorIndex = 5 'Blue
Case Is = 4
.ColorIndex = 4 'Green
Case Is = 2
.ColorIndex = 3 'Red
Case Is = 1
.ColorIndex = 7 'Pink
End Select
'.Pattern = xlSolid
.Pattern = xlGray25
End With
End If
Next cl
End Sub

Didn't find what you were looking for? Find more on vba code help Or get search suggestion and latest updates.