format number-date question

[driller]

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[Kassie]

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail

 

[driller]

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[Kassie]

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[Kassie]

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[driller]

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[driller]

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[Kassie]

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[driller]

thanks,

i forgot to give more details of my request. i am just on the preparatory
stage.

xls03 : rows 1 ~ 10 & columns A~J will not be included in the formatting.
The scheduling of items on succeeding rows and columns will grow indefinitely.

we can take K11:BZ1000 as the range where the red font date < today() is to
be located.

I suppose that a vb code will fit as the 4th prevalent condition.

the 3 conditional formats used are formulated :
1.) date < target
2.) date > target
3.) date > today()

sorry if i bothered your attention.

regards,

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail


:

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[Kassie]

No need to feel sorry!

Let me get this clear.

From K11:BZ1000 you can have a date anywhere. What else can you expect in
this range. Normally one would use one column for dates, and not the entire
sheet, that's why I am asking. Also, to have dates in K, L, M, N and so on,
isn't that a bit too much.?

As I say, I'm trying to get your thinking here. Thanks for the other
conditions as well, since I will have to incorporate all into the VB code.

--
HTH

Kassie

Replace xxx with hotmail

thanks,

i forgot to give more details of my request. i am just on the preparatory
stage.

xls03 : rows 1 ~ 10 & columns A~J will not be included in the formatting.
The scheduling of items on succeeding rows and columns will grow indefinitely.

we can take K11:BZ1000 as the range where the red font date < today() is to
be located.

I suppose that a vb code will fit as the 4th prevalent condition.

the 3 conditional formats used are formulated :
1.) date < target
2.) date > target
3.) date > today()

sorry if i bothered your attention.

regards,

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

:

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail


:

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[driller]

yeh its better to be clear,
On column J, i have the formula generated target dates for each row of items.
Columns K~BZ row 11 to 1000 will contains dates, some are formula generated
dates and generally finalized/overridden by manual input later.
These columns represent sequence of different milestone for each item.
also K10:BZ10 contains fix nominal dates as guide.

example
-----------------
Item 1 :
J11=31 MAR. 2011 <target date of last milestone>
V11=20 APR. 2009 <manually typed cell subject to 3 cond. format + code for
red font>
today() = 30 APR. 2009
-----------------

thus, V11<today(), the code will display a red font on V11.

also since 1st Conditional format will prevail as well, there will be a
display of blue color pattern on V11.
1.) date < target (20 APR. 2009 < 31 MAR. 2011)

thereby, a red font date [20 APR. 2009] on a blue color pattern cell will be
displayed on V11.

All 3 conditional format are colored pattern sensitive, no conditional
format will be applied on font.

I am not required to place all the 3 cond. format into a code.

thanks for a possible solution.

regards,

No need to feel sorry!

Let me get this clear.

From K11:BZ1000 you can have a date anywhere. What else can you expect in
this range. Normally one would use one column for dates, and not the entire
sheet, that's why I am asking. Also, to have dates in K, L, M, N and so on,
isn't that a bit too much.?

As I say, I'm trying to get your thinking here. Thanks for the other
conditions as well, since I will have to incorporate all into the VB code.

--
HTH

Kassie

Replace xxx with hotmail

thanks,

i forgot to give more details of my request. i am just on the preparatory
stage.

xls03 : rows 1 ~ 10 & columns A~J will not be included in the formatting.
The scheduling of items on succeeding rows and columns will grow indefinitely.

we can take K11:BZ1000 as the range where the red font date < today() is to
be located.

I suppose that a vb code will fit as the 4th prevalent condition.

the 3 conditional formats used are formulated :
1.) date < target
2.) date > target
3.) date > today()

sorry if i bothered your attention.

regards,

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

:

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail


:

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[Kassie]

OK, what colour pattern or font for condition 2 and condition 3
2 = Date > target date
3 = Date >today

To summarise, you require 4 conditions
consition 1 as per your example = Date < target date
2 and 3 as above
Condition 4 as per your example, date < today
Will work on that, and let you know

--
HTH

Kassie

Replace xxx with hotmail

yeh its better to be clear,
On column J, i have the formula generated target dates for each row of items.
Columns K~BZ row 11 to 1000 will contains dates, some are formula generated
dates and generally finalized/overridden by manual input later.
These columns represent sequence of different milestone for each item.
also K10:BZ10 contains fix nominal dates as guide.

example
-----------------
Item 1 :
J11=31 MAR. 2011 <target date of last milestone>
V11=20 APR. 2009 <manually typed cell subject to 3 cond. format + code for
red font>
today() = 30 APR. 2009
-----------------

thus, V11<today(), the code will display a red font on V11.

also since 1st Conditional format will prevail as well, there will be a
display of blue color pattern on V11.
1.) date < target (20 APR. 2009 < 31 MAR. 2011)

thereby, a red font date [20 APR. 2009] on a blue color pattern cell will be
displayed on V11.

All 3 conditional format are colored pattern sensitive, no conditional
format will be applied on font.

I am not required to place all the 3 cond. format into a code.

thanks for a possible solution.

regards,

No need to feel sorry!

Let me get this clear.

From K11:BZ1000 you can have a date anywhere. What else can you expect in
this range. Normally one would use one column for dates, and not the entire
sheet, that's why I am asking. Also, to have dates in K, L, M, N and so on,
isn't that a bit too much.?

As I say, I'm trying to get your thinking here. Thanks for the other
conditions as well, since I will have to incorporate all into the VB code.

--
HTH

Kassie

Replace xxx with hotmail

thanks,

i forgot to give more details of my request. i am just on the preparatory
stage.

xls03 : rows 1 ~ 10 & columns A~J will not be included in the formatting.
The scheduling of items on succeeding rows and columns will grow indefinitely.

we can take K11:BZ1000 as the range where the red font date < today() is to
be located.

I suppose that a vb code will fit as the 4th prevalent condition.

the 3 conditional formats used are formulated :
1.) date < target
2.) date > target
3.) date > today()

sorry if i bothered your attention.

regards,

:

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

:

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail


:

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[driller]

thanks if advance,

the 3 conditional formats used are formulated :
1.) date < target ---------blue pattern
2.) date > target ---------yellow pattern
3.) date > today() --------gray pattern

regards,

OK, what colour pattern or font for condition 2 and condition 3
2 = Date > target date
3 = Date >today

To summarise, you require 4 conditions
consition 1 as per your example = Date < target date
2 and 3 as above
Condition 4 as per your example, date < today
Will work on that, and let you know

--
HTH

Kassie

Replace xxx with hotmail

yeh its better to be clear,
On column J, i have the formula generated target dates for each row of items.
Columns K~BZ row 11 to 1000 will contains dates, some are formula generated
dates and generally finalized/overridden by manual input later.
These columns represent sequence of different milestone for each item.
also K10:BZ10 contains fix nominal dates as guide.

example
-----------------
Item 1 :
J11=31 MAR. 2011 <target date of last milestone>
V11=20 APR. 2009 <manually typed cell subject to 3 cond. format + code for
red font>
today() = 30 APR. 2009
-----------------

thus, V11<today(), the code will display a red font on V11.

also since 1st Conditional format will prevail as well, there will be a
display of blue color pattern on V11.
1.) date < target (20 APR. 2009 < 31 MAR. 2011)

thereby, a red font date [20 APR. 2009] on a blue color pattern cell will be
displayed on V11.

All 3 conditional format are colored pattern sensitive, no conditional
format will be applied on font.

I am not required to place all the 3 cond. format into a code.

thanks for a possible solution.

regards,

No need to feel sorry!

Let me get this clear.

From K11:BZ1000 you can have a date anywhere. What else can you expect in
this range. Normally one would use one column for dates, and not the entire
sheet, that's why I am asking. Also, to have dates in K, L, M, N and so on,
isn't that a bit too much.?

As I say, I'm trying to get your thinking here. Thanks for the other
conditions as well, since I will have to incorporate all into the VB code.

--
HTH

Kassie

Replace xxx with hotmail


:

thanks,

i forgot to give more details of my request. i am just on the preparatory
stage.

xls03 : rows 1 ~ 10 & columns A~J will not be included in the formatting.
The scheduling of items on succeeding rows and columns will grow indefinitely.

we can take K11:BZ1000 as the range where the red font date < today() is to
be located.

I suppose that a vb code will fit as the 4th prevalent condition.

the 3 conditional formats used are formulated :
1.) date < target
2.) date > target
3.) date > today()

sorry if i bothered your attention.

regards,

:

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

:

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail


:

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[Kassie]

Keeping your Conditional formatting as is, right click on your sheet tab,
select view code, and paste in the following:

PrivateSub Worksheet_Change(ByVal Target as Range)
Dim cRange as Range
Dim CellVal as Date
Dim RowNum as Long
If Target.Cells.count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set Watchrange = Range("K11:BZ1000")
RowNum = Target.Row
If Not Intersect (Target, Watchrange) Is Nothing Then
Select Case CellVal
Case is < Date
Target.Font.Colorindex = 3
End Select
End If
End Sub

Close the VB window, and try. Remember to save before exiting.

--
HTH

Kassie

Replace xxx with hotmail

thanks if advance,

the 3 conditional formats used are formulated :
1.) date < target ---------blue pattern
2.) date > target ---------yellow pattern
3.) date > today() --------gray pattern

regards,

OK, what colour pattern or font for condition 2 and condition 3
2 = Date > target date
3 = Date >today

To summarise, you require 4 conditions
consition 1 as per your example = Date < target date
2 and 3 as above
Condition 4 as per your example, date < today
Will work on that, and let you know

--
HTH

Kassie

Replace xxx with hotmail

yeh its better to be clear,
On column J, i have the formula generated target dates for each row of items.
Columns K~BZ row 11 to 1000 will contains dates, some are formula generated
dates and generally finalized/overridden by manual input later.
These columns represent sequence of different milestone for each item.
also K10:BZ10 contains fix nominal dates as guide.

example
-----------------
Item 1 :
J11=31 MAR. 2011 <target date of last milestone>
V11=20 APR. 2009 <manually typed cell subject to 3 cond. format + code for
red font>
today() = 30 APR. 2009
-----------------

thus, V11<today(), the code will display a red font on V11.

also since 1st Conditional format will prevail as well, there will be a
display of blue color pattern on V11.
1.) date < target (20 APR. 2009 < 31 MAR. 2011)

thereby, a red font date [20 APR. 2009] on a blue color pattern cell will be
displayed on V11.

All 3 conditional format are colored pattern sensitive, no conditional
format will be applied on font.

I am not required to place all the 3 cond. format into a code.

thanks for a possible solution.

regards,

:

No need to feel sorry!

Let me get this clear.

From K11:BZ1000 you can have a date anywhere. What else can you expect in
this range. Normally one would use one column for dates, and not the entire
sheet, that's why I am asking. Also, to have dates in K, L, M, N and so on,
isn't that a bit too much.?

As I say, I'm trying to get your thinking here. Thanks for the other
conditions as well, since I will have to incorporate all into the VB code.

--
HTH

Kassie

Replace xxx with hotmail


:

thanks,

i forgot to give more details of my request. i am just on the preparatory
stage.

xls03 : rows 1 ~ 10 & columns A~J will not be included in the formatting.
The scheduling of items on succeeding rows and columns will grow indefinitely.

we can take K11:BZ1000 as the range where the red font date < today() is to
be located.

I suppose that a vb code will fit as the 4th prevalent condition.

the 3 conditional formats used are formulated :
1.) date < target
2.) date > target
3.) date > today()

sorry if i bothered your attention.

regards,

:

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

:

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail


:

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[Rookie 1st class]

Offers Kassie a beverage of her choice... Thanks for your effort.
Lou

Keeping your Conditional formatting as is, right click on your sheet tab,
select view code, and paste in the following:

PrivateSub Worksheet_Change(ByVal Target as Range)
Dim cRange as Range
Dim CellVal as Date
Dim RowNum as Long
If Target.Cells.count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set Watchrange = Range("K11:BZ1000")
RowNum = Target.Row
If Not Intersect (Target, Watchrange) Is Nothing Then
Select Case CellVal
Case is < Date
Target.Font.Colorindex = 3
End Select
End If
End Sub

Close the VB window, and try. Remember to save before exiting.

--
HTH

Kassie

Replace xxx with hotmail

thanks if advance,

the 3 conditional formats used are formulated :
1.) date < target ---------blue pattern
2.) date > target ---------yellow pattern
3.) date > today() --------gray pattern

regards,

OK, what colour pattern or font for condition 2 and condition 3
2 = Date > target date
3 = Date >today

To summarise, you require 4 conditions
consition 1 as per your example = Date < target date
2 and 3 as above
Condition 4 as per your example, date < today
Will work on that, and let you know

--
HTH

Kassie

Replace xxx with hotmail


:

yeh its better to be clear,
On column J, i have the formula generated target dates for each row of items.
Columns K~BZ row 11 to 1000 will contains dates, some are formula generated
dates and generally finalized/overridden by manual input later.
These columns represent sequence of different milestone for each item.
also K10:BZ10 contains fix nominal dates as guide.

example
-----------------
Item 1 :
J11=31 MAR. 2011 <target date of last milestone>
V11=20 APR. 2009 <manually typed cell subject to 3 cond. format + code for
red font>
today() = 30 APR. 2009
-----------------

thus, V11<today(), the code will display a red font on V11.

also since 1st Conditional format will prevail as well, there will be a
display of blue color pattern on V11.
1.) date < target (20 APR. 2009 < 31 MAR. 2011)

thereby, a red font date [20 APR. 2009] on a blue color pattern cell will be
displayed on V11.

All 3 conditional format are colored pattern sensitive, no conditional
format will be applied on font.

I am not required to place all the 3 cond. format into a code.

thanks for a possible solution.

regards,

:

No need to feel sorry!

Let me get this clear.

From K11:BZ1000 you can have a date anywhere. What else can you expect in
this range. Normally one would use one column for dates, and not the entire
sheet, that's why I am asking. Also, to have dates in K, L, M, N and so on,
isn't that a bit too much.?

As I say, I'm trying to get your thinking here. Thanks for the other
conditions as well, since I will have to incorporate all into the VB code.

--
HTH

Kassie

Replace xxx with hotmail


:

thanks,

i forgot to give more details of my request. i am just on the preparatory
stage.

xls03 : rows 1 ~ 10 & columns A~J will not be included in the formatting.
The scheduling of items on succeeding rows and columns will grow indefinitely.

we can take K11:BZ1000 as the range where the red font date < today() is to
be located.

I suppose that a vb code will fit as the 4th prevalent condition.

the 3 conditional formats used are formulated :
1.) date < target
2.) date > target
3.) date > today()

sorry if i bothered your attention.

regards,

:

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

:

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail


:

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[Kassie]

My pleasure, so what happened to Driller?

--
HTH

Kassie

Replace xxx with hotmail

Offers Kassie a beverage of her choice... Thanks for your effort.
Lou

Keeping your Conditional formatting as is, right click on your sheet tab,
select view code, and paste in the following:

PrivateSub Worksheet_Change(ByVal Target as Range)
Dim cRange as Range
Dim CellVal as Date
Dim RowNum as Long
If Target.Cells.count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set Watchrange = Range("K11:BZ1000")
RowNum = Target.Row
If Not Intersect (Target, Watchrange) Is Nothing Then
Select Case CellVal
Case is < Date
Target.Font.Colorindex = 3
End Select
End If
End Sub

Close the VB window, and try. Remember to save before exiting.

--
HTH

Kassie

Replace xxx with hotmail

thanks if advance,

the 3 conditional formats used are formulated :
1.) date < target ---------blue pattern
2.) date > target ---------yellow pattern
3.) date > today() --------gray pattern

regards,

:

OK, what colour pattern or font for condition 2 and condition 3
2 = Date > target date
3 = Date >today

To summarise, you require 4 conditions
consition 1 as per your example = Date < target date
2 and 3 as above
Condition 4 as per your example, date < today
Will work on that, and let you know

--
HTH

Kassie

Replace xxx with hotmail


:

yeh its better to be clear,
On column J, i have the formula generated target dates for each row of items.
Columns K~BZ row 11 to 1000 will contains dates, some are formula generated
dates and generally finalized/overridden by manual input later.
These columns represent sequence of different milestone for each item.
also K10:BZ10 contains fix nominal dates as guide.

example
-----------------
Item 1 :
J11=31 MAR. 2011 <target date of last milestone>
V11=20 APR. 2009 <manually typed cell subject to 3 cond. format + code for
red font>
today() = 30 APR. 2009
-----------------

thus, V11<today(), the code will display a red font on V11.

also since 1st Conditional format will prevail as well, there will be a
display of blue color pattern on V11.
1.) date < target (20 APR. 2009 < 31 MAR. 2011)

thereby, a red font date [20 APR. 2009] on a blue color pattern cell will be
displayed on V11.

All 3 conditional format are colored pattern sensitive, no conditional
format will be applied on font.

I am not required to place all the 3 cond. format into a code.

thanks for a possible solution.

regards,

:

No need to feel sorry!

Let me get this clear.

From K11:BZ1000 you can have a date anywhere. What else can you expect in
this range. Normally one would use one column for dates, and not the entire
sheet, that's why I am asking. Also, to have dates in K, L, M, N and so on,
isn't that a bit too much.?

As I say, I'm trying to get your thinking here. Thanks for the other
conditions as well, since I will have to incorporate all into the VB code.

--
HTH

Kassie

Replace xxx with hotmail


:

thanks,

i forgot to give more details of my request. i am just on the preparatory
stage.

xls03 : rows 1 ~ 10 & columns A~J will not be included in the formatting.
The scheduling of items on succeeding rows and columns will grow indefinitely.

we can take K11:BZ1000 as the range where the red font date < today() is to
be located.

I suppose that a vb code will fit as the 4th prevalent condition.

the 3 conditional formats used are formulated :
1.) date < target
2.) date > target
3.) date > today()

sorry if i bothered your attention.

regards,

:

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

:

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail


:

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,

 

[driller]

works good for the 1st time...Is there a way to make it work like a static
4th condition...
i tried a 1st test by changing the font color using the default toolbar and
the code doesn't seem to sustain the requested red font.
It will return to red font only if i go on every cell and hit *F2*.
Is there any safe way to avoid any user default on this guiding sheet?

My pleasure, so what happened to Driller?

--
HTH

Kassie

Replace xxx with hotmail

Offers Kassie a beverage of her choice... Thanks for your effort.
Lou

Keeping your Conditional formatting as is, right click on your sheet tab,
select view code, and paste in the following:

PrivateSub Worksheet_Change(ByVal Target as Range)
Dim cRange as Range
Dim CellVal as Date
Dim RowNum as Long
If Target.Cells.count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set Watchrange = Range("K11:BZ1000")
RowNum = Target.Row
If Not Intersect (Target, Watchrange) Is Nothing Then
Select Case CellVal
Case is < Date
Target.Font.Colorindex = 3
End Select
End If
End Sub

Close the VB window, and try. Remember to save before exiting.

--
HTH

Kassie

Replace xxx with hotmail


:

thanks if advance,

the 3 conditional formats used are formulated :
1.) date < target ---------blue pattern
2.) date > target ---------yellow pattern
3.) date > today() --------gray pattern

regards,

:

OK, what colour pattern or font for condition 2 and condition 3
2 = Date > target date
3 = Date >today

To summarise, you require 4 conditions
consition 1 as per your example = Date < target date
2 and 3 as above
Condition 4 as per your example, date < today
Will work on that, and let you know

--
HTH

Kassie

Replace xxx with hotmail


:

yeh its better to be clear,
On column J, i have the formula generated target dates for each row of items.
Columns K~BZ row 11 to 1000 will contains dates, some are formula generated
dates and generally finalized/overridden by manual input later.
These columns represent sequence of different milestone for each item.
also K10:BZ10 contains fix nominal dates as guide.

example
-----------------
Item 1 :
J11=31 MAR. 2011 <target date of last milestone>
V11=20 APR. 2009 <manually typed cell subject to 3 cond. format + code for
red font>
today() = 30 APR. 2009
-----------------

thus, V11<today(), the code will display a red font on V11.

also since 1st Conditional format will prevail as well, there will be a
display of blue color pattern on V11.
1.) date < target (20 APR. 2009 < 31 MAR. 2011)

thereby, a red font date [20 APR. 2009] on a blue color pattern cell will be
displayed on V11.

All 3 conditional format are colored pattern sensitive, no conditional
format will be applied on font.

I am not required to place all the 3 cond. format into a code.

thanks for a possible solution.

regards,

:

No need to feel sorry!

Let me get this clear.

From K11:BZ1000 you can have a date anywhere. What else can you expect in
this range. Normally one would use one column for dates, and not the entire
sheet, that's why I am asking. Also, to have dates in K, L, M, N and so on,
isn't that a bit too much.?

As I say, I'm trying to get your thinking here. Thanks for the other
conditions as well, since I will have to incorporate all into the VB code.

--
HTH

Kassie

Replace xxx with hotmail


:

thanks,

i forgot to give more details of my request. i am just on the preparatory
stage.

xls03 : rows 1 ~ 10 & columns A~J will not be included in the formatting.
The scheduling of items on succeeding rows and columns will grow indefinitely.

we can take K11:BZ1000 as the range where the red font date < today() is to
be located.

I suppose that a vb code will fit as the 4th prevalent condition.

the 3 conditional formats used are formulated :
1.) date < target
2.) date > target
3.) date > today()

sorry if i bothered your attention.

regards,

:

I suggest you send me your sheet, with instructions on which cells has to
change. Without detailed info, I really cannot help you here?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,

this is a Time Schedule workbook:
One specific worksheet (i.e. Sheet1 only) needs to adapt with the red font
dates < today().

thank you.

:

OK, not knowing exactly what you want to achieve, and where, here is an
answer given on one of these posts some time ago. Of course you will have to
adapt to suit your own needs. Play aroun, and see whether you come right.
If not, repost with more specific requirements - cell addresses etc.

This was posted by Mike H on 25/5/2007!

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

Mike

--
HTH

Kassie

Replace xxx with hotmail


:

ok, do you mean it is not possible in ordinary custom format?

do you have a macro to share?

thanks.

:

Driller, you'll have to resort to VB my friend. Do you know how to record
macros?

--
HTH

Kassie

Replace xxx with hotmail


:

Kassie,
the 3 conditions were used already. Is it possible in the custom formatting ?

regards,

:

That is what conditional format is there for? Why not use it?

--
HTH

Kassie

Replace xxx with hotmail


:

Hello,

without the use of conditional formatting, is it possible to format the date
turn red font if it is before today().

same like when numbers are treated as negative.

something like...
[red if < today()] dd-mmm-yy

regards,