Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Icolor, target,range

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Icolor, target,range

Resolved · Medium Priority · Version 2003

Reply Wed 20 May 2009, 14:06Delegate Scott said...

Scott has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Access VBA course

Icolor, target,range

How do I get vba to shade a cell using VBA dependant on the text in another column (typically the column immediately to the right?

For upcoming training course dates see: Pricing & availability

Reply Fri 22 May 2009, 13:46Trainer Carlos said...

RE: icolor, target,range

Hi Scott

The code below looks at text in column B of a workbook and whatever text it finds it colours the adjacent cell in column A either Green, Red or leaves the cell Uncoloured if the words don't match.

Sub ColourFormatByText()

Dim intCounter As Integer

For intCounter = 2 To 20

Select Case Cells(intCounter, 2).Value

Case "Hello"
Cells(intCounter, 1).Interior.ColorIndex = 3

Case "Goodbye"
Cells(intCounter, 1).Interior.ColorIndex = 4

Case Else
Cells(intCounter, 1).Interior.ColorIndex = xlNone

End Select

Next intCounter

End Sub

I have attached a Workbook so you can see the code at work

Hope this helps

Carlos

Attached files...

FormatCells.xls

Reply Fri 22 May 2009, 20:14Delegate Scott said...

RE: icolor, target,range

Hi Carlos,
I can see what it should do, but no joy on my pc! I have the VBA add-ins selected, could I be missing something?

Rgds

Scott

Reply Wed 27 May 2009, 11:14Trainer Carlos said...

RE: icolor, target,range

Hi Scott

Are you saying the workbook I sent you is not working on your PC?

Regards

Carlos

Reply Thu 28 May 2009, 21:06Delegate Scott said...

RE: icolor, target,range

Hi Carlos,
Yes, coming down to Southwark Tuesday/Wednesday for Access courses if you are about?

Regards
Scott

Reply Fri 29 May 2009, 09:12Trainer Carlos said...

RE: icolor, target,range

Hi Scott

No I'm not in Southwark next week. I am training at other sites.

Since the workbook I sent you does not work on your PC, there seems to be a software problem with Excel on your PC and you may need to contact your IT department to fix it.

Regards

Carlos