Updating Excel Code
 

HI can some please assist with this issue on updating Excel I have written
some code in Excel thet when you put in a certain letter in a cell it should
turn the cell a different colour.

Now this does not happen until you go in to view code and click run, WHY?

I have set calculations to automatic in options but I still have to go into
view code

Any Ideas many thanks





--
N/A

Updating Excel Code
 

Hi Joel,

Try posting your code.


---
Regards,
Norman



"Joel" wrote in message
...
HI can some please assist with this issue on updating Excel I have written
some code in Excel thet when you put in a certain letter in a cell it
should
turn the cell a different colour.

Now this does not happen until you go in to view code and click run, WHY?

I have set calculations to automatic in options but I still have to go
into
view code

Any Ideas many thanks





--
N/A

Updating Excel Code
 

Sounds as though you have not put it in the correct module.

Have you tried conditions formatting,
http://www.contextures.com/xlCondFormat01.html

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joel" wrote in message
...
HI can some please assist with this issue on updating Excel I have written
some code in Excel thet when you put in a certain letter in a cell it
should
turn the cell a different colour.

Now this does not happen until you go in to view code and click run, WHY?

I have set calculations to automatic in options but I still have to go
into
view code

Any Ideas many thanks





--
N/A

Updating Excel Code
 

Hi Norman this be the code

Private Sub Worksheet_Calculate()

Dim oCell As Range
For Each oCell In Range("V3:GU36")
Select Case oCell.Value
Case Is < 1
oCell.Interior.ColorIndex = xlNone

' This is for Holidays UPPERCASE
' The Colour for this Cell is Green
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "H"
oCell.Interior.ColorIndex = 10
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Half Day Holidays UPPERCASE
' The Colour for this Cell is Green
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "H/2"
oCell.Interior.ColorIndex = 10
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Holidays lowercase
' The Colour for this Cell is Green
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "h"
oCell.Interior.ColorIndex = 10
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Half Day Holidays lowercase
' The Colour for this Cell is Green
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "h/2"
oCell.Interior.ColorIndex = 10
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' End of the Holiday Section

'Start of Bank Holidays

' This is for Half Days Bank Holiday lowercase
' The Colour for this Cell is Pink
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "bh/2"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Half Days Bank Holiday UPPERCASE
' The Colour for this Cell is Pink
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "BH/2"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Bank Holiday lowercase
' The Colour for this Cell is Pink
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "bh"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Bank Holiday UPPERCASE
' The Colour for this Cell is Pink
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "BH"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

End Select
Next oCell
End Sub
--
N/A


"Norman Jones" wrote:

Hi Joel,

Try posting your code.


---
Regards,
Norman



"Joel" wrote in message
...
HI can some please assist with this issue on updating Excel I have written
some code in Excel thet when you put in a certain letter in a cell it
should
turn the cell a different colour.

Now this does not happen until you go in to view code and click run, WHY?

I have set calculations to automatic in options but I still have to go
into
view code

Any Ideas many thanks





--
N/A

Updating Excel Code
 

The Calculate event wouldn't necessarily trigger merely by changing a value
of a cell unless referenced by some other formula. But if/when it does work
I magine it would take a long time to run with all those cells to loop
through.

Try the Change event, eg

rename Private Sub Worksheet_Calculate() to
Private Sub myFormat(oCell As Range)

comment out Dim oCell as range and the For...Next pair

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range

On Error GoTo errExit
Set rng = Intersect(Range(("V3:GU36"),Target)
If Not rng Is Nothing Then
For Each cel In rng
myFormat cel
Next
End If

errExit:
End Sub

You might also want to include the following in the Select case -

Case Else
oCell.Interior.ColorIndex = xlNone
oCell.Font.ColorIndex = xlAutomatic
oCell.Font.Bold = False

Bob's link will probably serve you better.

Regards,
Peter T


"Joel" wrote in message
...
Hi Norman this be the code

Private Sub Worksheet_Calculate()

Dim oCell As Range
For Each oCell In Range("V3:GU36")
Select Case oCell.Value
Case Is < 1
oCell.Interior.ColorIndex = xlNone

' This is for Holidays UPPERCASE
' The Colour for this Cell is Green
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "H"
oCell.Interior.ColorIndex = 10
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Half Day Holidays UPPERCASE
' The Colour for this Cell is Green
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "H/2"
oCell.Interior.ColorIndex = 10
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Holidays lowercase
' The Colour for this Cell is Green
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "h"
oCell.Interior.ColorIndex = 10
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Half Day Holidays lowercase
' The Colour for this Cell is Green
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "h/2"
oCell.Interior.ColorIndex = 10
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' End of the Holiday Section

'Start of Bank Holidays

' This is for Half Days Bank Holiday lowercase
' The Colour for this Cell is Pink
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "bh/2"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Half Days Bank Holiday UPPERCASE
' The Colour for this Cell is Pink
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "BH/2"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Bank Holiday lowercase
' The Colour for this Cell is Pink
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "bh"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

' This is for Bank Holiday UPPERCASE
' The Colour for this Cell is Pink
' The Color of the Font is White
' The font in the cell is emboldened

Case Is = "BH"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True

End Select
Next oCell
End Sub
--
N/A


"Norman Jones" wrote:

Hi Joel,

Try posting your code.


---
Regards,
Norman



"Joel" wrote in message
...
HI can some please assist with this issue on updating Excel I have
written
some code in Excel thet when you put in a certain letter in a cell it
should
turn the cell a different colour.

Now this does not happen until you go in to view code and click run,
WHY?

I have set calculations to automatic in options but I still have to go
into
view code

Any Ideas many thanks





--
N/A