Search : in
By :

Refresh colour in conditional formating excel

Last answer on Aug 16, 2009 4:31:25 am BST confused, on Aug 13, 2009 6:32:53 am BST 
 Report this message to moderators

Hello,

I have used the below vba code to format a drop down cell based on whether its pass or fail or block in excel(2000)
'START OF CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target
Set WatchRange = Range("F18:F197")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Pass"
Target.Interior.ColorIndex = 42
Case "Fail"
Target.Interior.ColorIndex = 7
Case "Block"
Target.Interior.ColorIndex = 5
Case "TBD"
Target.Interior.ColorIndex = 29
End Select
End If
End Sub
'END OF CODE
The problem is if i change the colour value of "Block" in the code from 5 to 10 the colour does not get automatically updated in the sheet. I need to select "Block" again from the drop down menu for it to work.

Is there a way i can automatically refresh it

Configuration: Windows XP
Firefox 3.0.13

Best answers for « Refresh colour in conditional formating excel » in :
Days lapsed conditional formatting VB Show
Conditional Format - More than 3 colours Show
Unlimited Conditional Formatting Show Unlimited Conditional Formatting Introduction Principle Code Introduction The limitation of MFC (Conditional Formatting) to Excel version 2007 is less than 3. This is very short. Having seen many applications to push the limit, I am...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
[VBA] A function that returns the color of an active MFC Show[VBA] A function that returns the color of an active MFC This function returns the value of the active format in conditional formatting. With the function below, two values can be returned. Mode = 0: the value of Interior.ColorIndex...
Colouring cells on conditions ShowColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
Download LiveProject Free Project Viewer ShowLiveProject is an application of visualization of file mpp free. LiveProject introduces no degradation of the file mpp during exportation in format Excel or html. With LiveProject, you will be able to print your own documents. The use of LiveProject...
Download HDD Low Level Format Tool Show
Download Excel Password Recovery Master Show
TIF format ShowTIF format The TIF format (Tagged Image File Format) is a bitmap (raster) graphic file format. It was developed in 1987 by Aldus (now belonging to Adobe). The latest specifications (Revision 6.0) were published in 1992. Characteristics of the TIF...
Spreadsheets - The Excel Interface ShowExcel Introduction Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel...
Formatting - Formatting a hard drive ShowBefore trying to understand formatting, you first need to understand how a hard drive works. Many people do not distinguish low-level formatting (also called physical formatting) from high-level formatting (also called logical formatting). Even...
Ask your question Reply

1

 Excelguru, on Aug 16, 2009 4:31:25 am BST

Try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:F7")

'If Not Intersect(Target, WatchRange) Is Nothing Then
For Each cll In WatchRange
Select Case cll.Value
Case "Pass"
cll.Interior.ColorIndex = 6
Case "Fail"
cll.Interior.ColorIndex = 7
Case "Block"
cll.Interior.ColorIndex = 5
Case "TBD"
cll.Interior.ColorIndex = 9
Case Else
cll.Interior.ColorIndex = 0
End Select
Next
'End If
End Sub Winners are losers who got up and gave it one more try. -Dennis DeYoung
My Interests are financial Modelling and custom excel development.

   
Reply to Excelguru
Ask your question Reply
They need your help