Excel Conditional Formatting almost but not quite

[MrsNic]

I have used the following code to apply conditional formatting in a spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Target
Select Case myCell.Value
Case Is = "Match"
Range(myCell, myCell.Offset(, 0)).Interior.ColorIndex = 27
Range(myCell, myCell.Offset(, -16)).Interior.ColorIndex = 27
Case Is = "QS"
Range(myCell, myCell.Offset(, 9)).Interior.ColorIndex = 45
Range(myCell, myCell.Offset(, -7)).Interior.ColorIndex = 45
Case Is = "MS"
Range(myCell, myCell.Offset(, 6)).Interior.ColorIndex = 45
Range(myCell, myCell.Offset(, -1)).Interior.ColorIndex = 45
Case Else
Range(myCell, myCell.Offset(, 6)).Interior.ColorIndex = xlNone
End Select
Next myCell
End Sub

It works perfectly if I type in the words "Match" or "QS". Is it possible to get the code to look at what is already in the cell?

Also Interior.ColorIndex changes the fill color what is the code to change the font color.

Many thanks, I look forward to hearing your ideas.

Cath

 

[SkipVought]

Hi,

Why are you using VBA, when Conditional Formatting is a native Excel feature?

BTW, VBA questions are addressed in forum707. But again, WHY?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrsNic]

I have used VB because when I initially searched for a solution a previous post recommended this type of solution. I thought it would be ok to post it in this forum as it is vb in an Excel spreadsheet.

How would you recommend I complete this in Excel? I am using 2007 and I am having some difficulty getting it to fill in the entire row.

I have just seen a recent post covering the same thing so will have a look at that.

Cath

 

[xlbo]

It's only working when you add an entry as you are using the worksheet "CHANGE" event which is only fired when the worksheet is, well, changed....

The only reason to use code for conditional formatting is if you have lots of different criteria to check for

you seem to have just 3 criteria so should be able to easily accomplish this using native conditional formatting

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[strongm]

>if you have lots of different criteria to check for

A limitation removed in Excel 2007, which MrsNic has said she is using.

 

[xlbo]

Which still booils down to my last point that it should be easily able to be accomplished in native CF...whether 2007 or not!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MrsNic]

Thanks for everyone who took the time to answer. I have almost completed the task using basic CF. The final thing I have to work out is how to change the text colour of the whole row if a cell in a particular column is not blank.

I have used the 'If not blank option to change the text colour in that cell but can anyone tell me how to achive the same effect as I did with OFFSET in BV?

Again thanks for all you help

 

[SkipVought]

You can use an ABSOLUTE reference for the COLUMN and RELATIVE for the ROW, like $C2, if the data you're referencing is ALWAYS in column C and your CF cell in somewhere in row 2.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]