I am writing VBA in Excel 2003 sheet that is linked to SharePoint 2007 List. What I am trying to achieve is to set five value options to the cells in certain range and cell color should be changed depending on the value selected every time a user changes a value of the cell. Since Conditional formatting function of Excel 2003 only supports three conditions, I wrote the following code to perform the conditional formatting, and it worked fine when I changed cell value in Excel itself. However, it gives me "Type mismatch" error when I try to update the Excel cell values by Synchronizing with the linked SharePoint. Any idea to make it work?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("W1:CS200")) Is Nothing Then

Select Case Target

Case "Not Feasible/Applicable (Black)"

icolor = 1

Case "No Plan Available (Red)"

icolor = 3

Case "Have a plan (Yellow)"

icolor = 6

Case "Need Help (Pink)"

icolor = 7

Case "Implemented (Green)"

icolor = 50

Case Else

'Do nothing

End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

Hi

Can you specify the line where you are getting this error

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Thank you for your prompt response. The following were the steps I took for testing.

(1) I inserted Debug.Print in my code for debugging (see below).
(2) I updated one cell value to "Have a plan (Yellow)" in the linked SharePoint list.
(3) I clicked "Synchronized icon" in the Excel sheet.
(4) The run time error occurred and debug pointer is pointing the line -> Case "Not Feasible/Applicable (Black)". I assume that this is the first line of "Select Case" section". Immediate window shows: "Have a plan (Yellow)".

I appreciate if you could help me on this error.