Cell Shading + VB

[bluenose5709]

Hi,I am working with Excel 2003 and would liek to automate cell shading.my criteria is text based and ther are 8 permitations so conditional formatting is out of the question.utilising VB i have managed ot gt teh cells to colour as required when i populate with the the relevant info using the code below:" Private Sub Worksheet_Change(ByVal Target As Range)Dim vValue As VariantvValue = Target.ValueOn Error Resume NextIf vValue = "L" ThenTarget.Interior.Color = RGB(0, 255, 0)End IfIf vValue = "CE" ThenTarget.Interior.Color = RGB(205, 204, 153)End IfIf vValue = "XE" ThenTarget.Interior.Color = RGB(153, 153, 255)End IfIf vValue = "HG" ThenTarget.Interior.Color = RGB(255, 102, 0)End IfIf vValue = "SG" ThenTarget.Interior.Color = RGB(0, 255, 255)End IfIf vValue = "AT" ThenTarget.Interior.Color = RGB(255, 255, 0)End IfIf vValue = " " ThenTarget.Interior.Color = RGB(0, 0, 0)End IfIf vValue = "l" ThenTarget.Interior.Color = RGB(0, 255, 0)End IfIf vValue = "ce" ThenTarget.Interior.Color = RGB(205, 204, 153)End IfIf vValue = "xe" ThenTarget.Interior.Color = RGB(153, 153, 255)End IfIf vValue = "hg" ThenTarget.Interior.Color = RGB(255, 102, 0)End IfIf vValue = "sg" ThenTarget.Interior.Color = RGB(0, 255, 255)End IfIf vValue = "at" ThenTarget.Interior.Color = RGB(255, 255, 0)End IfIf vValue = "d" ThenTarget.Interior.Color = RGB(255, 0, 0)End IfEnd Sub "However, what i would like to happen is for the cell to return to having no shading (or revert to white) when the contents of the cell is deleted.If there is anyone who can help me with this, it would be hugely appreciated.Thank youRegardsArran

 

[VoG]

Perhaps

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vValue As Variant
vValue = Target.Value
On Error Resume Next
If vValue = "L" Then
    Target.Interior.Color = RGB(0, 255, 0)
End If
If vValue = "CE" Then
    Target.Interior.Color = RGB(205, 204, 153)
End If
If vValue = "XE" Then
    Target.Interior.Color = RGB(153, 153, 255)
End If
If vValue = "HG" Then
    Target.Interior.Color = RGB(255, 102, 0)
End If
If vValue = "SG" Then
    Target.Interior.Color = RGB(0, 255, 255)
End If
If vValue = "AT" Then
    Target.Interior.Color = RGB(255, 255, 0)
End If
If vValue = " " Then
    Target.Interior.Color = RGB(0, 0, 0)
End If
If vValue = "l" Then
    Target.Interior.Color = RGB(0, 255, 0)
End If
If vValue = "ce" Then
    Target.Interior.Color = RGB(205, 204, 153)
End If
If vValue = "xe" Then
    Target.Interior.Color = RGB(153, 153, 255)
End If
If vValue = "hg" Then
    Target.Interior.Color = RGB(255, 102, 0)
End If
If vValue = "sg" Then
    Target.Interior.Color = RGB(0, 255, 255)
End If
If vValue = "at" Then
    Target.Interior.Color = RGB(255, 255, 0)
End If
If vValue = "d" Then
    Target.Interior.Color = RGB(255, 0, 0)
End If
If vValue = "" Then
    Target.Interior.ColorIndex = xlNone
End If
End Sub

You might be better using Select Case rather than all those Ifs.

 

[bluenose5709]

VoG

Thank you, this works a dream. However, you will have to educate me on the use of "Select Case" rather than "If"


I'm always open to better ways of doing business.


My next quest is a little confusing, I have not yet quite figured out how I'm going to do it.


Basically I have a worksheet that has one column for every day of the year. There are many rows with related data. A simple count all or count if is used to show column totals of the various entries. On a separate worksheet, but in the same workbook I have a report that will be used to show totals as at a specific day, however, this is where I'm currently a little unsure.


In an ideal world, I would like to automate the workbook as much as possible. I would like the "Report" to recognise what day it is, and pull the data from that specific column automatically, rather than me copying the data othe day I wish to print the report.


Any suggestions a appreciated and welcomed


Thank you


Regards,
Arran

 

[VoG]

Using Select Case

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vValue As Variant
vValue = Target.Value
Select Case vValue
    Case "L": Target.Interior.Color = RGB(0, 255, 0)
    Case "CE": Target.Interior.Color = RGB(205, 204, 153)
    Case "XE": Target.Interior.Color = RGB(153, 153, 255)
    Case "HG": Target.Interior.Color = RGB(255, 102, 0)
    Case "SG": Target.Interior.Color = RGB(0, 255, 255)
    Case "AT": Target.Interior.Color = RGB(255, 255, 0)
    Case " ": Target.Interior.Color = RGB(0, 0, 0)
    Case "l": Target.Interior.Color = RGB(0, 255, 0)
    Case "ce": Target.Interior.Color = RGB(205, 204, 153)
    Case "xe": Target.Interior.Color = RGB(153, 153, 255)
    Case "hg": Target.Interior.Color = RGB(255, 102, 0)
    Case "sg": Target.Interior.Color = RGB(0, 255, 255)
    Case "at": Target.Interior.Color = RGB(255, 255, 0)
    Case "d": Target.Interior.Color = RGB(255, 0, 0)
    Case "": Target.Interior.ColorIndex = xlNone
End Select
End Sub

Please start a new thread for your new question.