I have a spreadsheet that contains data I import from various sources. Once the import is complete I need to colour various cells based on their contents. Normally I'd use conditional formatting, but there are more than 3 conditions so I need to use VBA (preferrably that i can turn into a macro and assign to a button to click once I've finished the imports each time)

eg: Everytime a cell has "RED" in it, colour it red - then the same with various other colours.

Google has found me a change event piece of code but it doesnt work when you're cutting and pasting some info. Can anyone help?

I don't know the answer, but a search found this:
http://en.kioskea.net/forum/affich-21716-conditional-formatting-more-than-3-in-excel

... was it the same that you already had?

Even when you paste 'values only'?

I don't know the answer, but a search found this:
http://en.kioskea.net/forum/affich-21716-conditional-formatting-more-than-3-in-excel

... was it the same that you already had?

Thats similar to the one I found - Basically that makes the change whenever a call is changed, but doent like it when you cut and paste in data. I cant paste values only as I lose quite a bit of formatting, plus its coming from different sources which is all automatic.

Thats similar to the one I found - Basically that makes the change whenever a call is changed, but doent like it when you cut and paste in data. I cant paste values only and I lose quite a bit of formatting, plus its coming from different sources which is all automatic.

Can you not paste into a different sheet and then in your VBA, copy and paste values only?

Just a thought ...

Rather than cutting/pasting into the formatted worksheet, can you not put the data into a parallel sheet/range and have your formatted cells containing = 'parallel cell'?

You need the code under the Workbook - change section in Visual Basic editor

This code colours cells red if the contain the word 'red' and are in column A.

No doubt it's a bit rough and ready - but I'm self-taught and very rusty!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim blueRange As Range
Dim greenRange As Range
Dim redRange As Range

Set redRange = Nothing
Set greenRange = Nothing
Set blueRange = Nothing

For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 1).Value = "red" Then
If redRange Is Nothing Then
Set redRange = (Cells(i, 1))

Else
Set redRange = Union(redRange, Cells(i, 1))
End If
End If

Next
If Not (redRange Is Nothing) Then
redRange.Interior.ColorIndex = 3
End If
If Not (blueRange Is Nothing) Then
blueRange.Interior.ColorIndex = 4
End If

End Sub

ETA - couldn't be bothered coding for green and blue as presumably it was a simplified example.

Just a thought ...

Rather than cutting/pasting into the formatted worksheet, can you not put the data into a parallel sheet/range and have your formatted cells containing = 'parallel cell'?

The fromat comes with the data - the problem area seems to be an import from SharePoint. Its just a blank excel worksheet, then I run a couple of imports and a bit of cuttign and pasting too and I have the complete report. I've just been asked to put colours in and its doing my head in!

The code I've posted works - but it will need adapting to suit your needs.

What exactly is it you need to colour, and where on the sheet is the data?

Thanks little buzz - I'll give that a whirl, looks a lot more complicated than the code I was trying
Sub Colour()
Dim c As Range
For Each c In ActiveSheet.UsedRange
Select Case UCase(c.Value)
Case "RED": c.Interior.ColorIndex = 3
Case "YELLOW": c.Interior.ColorIndex = 6
End Select
Next c
End Sub

The key thing is to make it execute when the sheet changes - put the code on the sheet object, not in a module and make sure it starts

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Then it will work when you cut and paste

Its basically highlighting certain cells - if a cell has the word GREEN in it turn it green etc. The cells can be anywhere and will always be in different places (i'm making the template)

It might be better to create is in a module then and just run it manually as required - if the data can be anywhere you will have to code to check the whole sheet for them - and it will do it whenever you change anything, which will make the s/sheet quite slow

OK - Breakthrough!

Rookie mistake on my part - i'm not using my computer and didnt have events enabled!

(In the Visual Basic Editor, press CTRL + G to open the Immediate Window then enter

Application.EnableEvents = True

then hit enter)

Got that from another forum - Now even my code works (I say 'my' I also nicked it from another site). It also executes when the sheet changes.

Only problem now is that some of my conditions are more than one word, and they dont work (ie. where "RED" works, "RED to AMBER" doesnt)

Once imported, the sheet will not change again (its a report) so speed isnt an issue.

Application.ReplaceFormat.Interior.ColorIndex = 4
Cells.Replace What:="GREEN", Replacement:="GREEN", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

Only problem now is that some of my conditions are more than one word, and they dont work (ie. where "RED" works, "RED to AMBER" doesnt)

Ignore that - I wasnt using all caps in my CASE statement. Now works ok.

Thanks everyone!

This is untested but this may trap imported data and edited text

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End sub