Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Need more CF capacity
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default Need more CF capacity
The conditional formatting limit is three conditions. I
am using all three, as follows:
formula conditional format
=RIGHT(G38,2)="ot font bold, background lt. green
=RIGHT(G38,2)="la" font bold, background lavander
=RIGHT(G38,2)="ls" font bold, background green

These CFs are good for any cell in the worksheet.

I need additional CF formatting on this worksheet, for
example, I need to add "ce" and "ct". What would the code
be, and where would I put it?

Thanks, Phil

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 41
Default Need more CF capacity
Phil,

You'll need to use some VBA code to do this. Add this to
the worksheet module of the worksheet in question (right
click tab and select View Code)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strTemp As String
If Not IsEmpty(Target) Then
strTemp = Right(Target.Cells(1).Value, 2)
Select Case strTemp
Case "ot"
Target.Interior.ColorIndex = 4
Target.Font.Bold = True
Case "la"
Target.Interior.ColorIndex = 13
Target.Font.Bold = True
Case "ls"
Target.Interior.ColorIndex = 10
Target.Font.Bold = True
Case "ce"
Target.Interior.ColorIndex = 7
Target.Font.Bold = True
Case "ct"
Target.Interior.ColorIndex = 8
Target.Font.Bold = True
Case Else
Target.Interior.ColorIndex = xlColorIndexNone
Target.Font.Bold = False
End Select
End If
End Sub

Look up ColorIndex in VBA help to see which number
represents each color.

Cheers,
Dave
-----Original Message-----
The conditional formatting limit is three conditions. I
am using all three, as follows:
formula conditional format
=RIGHT(G38,2)="ot font bold, background lt. green
=RIGHT(G38,2)="la" font bold, background lavander
=RIGHT(G38,2)="ls" font bold, background green

These CFs are good for any cell in the worksheet.

I need additional CF formatting on this worksheet, for
example, I need to add "ce" and "ct". What would the code
be, and where would I put it?

Thanks, Phil

.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default Need more CF capacity
The code will act when the user makes the entry, and the
cell in which they make the entry is formatted.
-----Original Message-----
When do you want it to happen. Will the user enter one
of those values
(Worksheet_change event). Will they be produced through
a formula
(Calculate Event).

Select Case Right(lcase(Cell.value),2)
Case "ot"
Cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True Case "ce"
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select


If the Change event, you can do

cell = Target

If the Calculate event

set rng = me.cells.specialcells(xlFormulas,xlTextValues)
for each cell in rng


Next

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
The conditional formatting limit is three conditions. I
am using all three, as follows:
formula conditional format
=RIGHT(G38,2)="ot font bold, background lt. green
=RIGHT(G38,2)="la" font bold, background lavander
=RIGHT(G38,2)="ls" font bold, background green

These CFs are good for any cell in the worksheet.

I need additional CF formatting on this worksheet, for
example, I need to add "ce" and "ct". What would the
code
be, and where would I put it?

Thanks, Phil



.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default Need more CF capacity
Tom, when a user enters, say, ot, as the right-most two
characters, only that cell is formatted according to the
code, correct? Any other cell will be unaffected? (code
for other cases notwithstanding). The worksheet has a
variety of other data cells and cell formats.
-----Original Message-----
Dave Ramage basically produced the same code as I and
packaged it all under
the change event which is what you want.

but for my code it would be:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
Select Case Right(LCase(cell.Value), 2)
Case "ot"
cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select
Next

End Sub

Remove any conditional formatting. You can check the
colorindex property
in VBA help for mapping a color to the color index or run
code like this on
a blank sheet

Sub aa_tester11()
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 2).Value = i
Next

End Sub



--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
The code will act when the user makes the entry, and the
cell in which they make the entry is formatted.
-----Original Message-----
When do you want it to happen. Will the user enter one
of those values
(Worksheet_change event). Will they be produced
through
a formula
(Calculate Event).

Select Case Right(lcase(Cell.value),2)
Case "ot"
Cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True Case "ce"
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select


If the Change event, you can do

cell = Target

If the Calculate event

set rng = me.cells.specialcells
(xlFormulas,xlTextValues)
for each cell in rng


Next

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
The conditional formatting limit is three
conditions. I
am using all three, as follows:
formula conditional format
=RIGHT(G38,2)="ot font bold, background lt.
green
=RIGHT(G38,2)="la" font bold, background
lavander
=RIGHT(G38,2)="ls" font bold, background green

These CFs are good for any cell in the worksheet.

I need additional CF formatting on this worksheet,
for
example, I need to add "ce" and "ct". What would the
code
be, and where would I put it?

Thanks, Phil



.



.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Need more CF capacity
NO.
as written it would change any other cell not containing one of the values
to no background color and not bold. That was there to handle the case
where you delete a cell that was formatted - however, if there are other
cells you want untouched, then you can't have the Case Else and least not
without further checks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
Select Case Right(LCase(cell.Value), 2)
Case "ot"
cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
' Case Else
' cell.Interior.ColorIndex = xlNone
' cell.Font.Bold = False
End Select
Next

End Sub
--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
Tom, when a user enters, say, ot, as the right-most two
characters, only that cell is formatted according to the
code, correct? Any other cell will be unaffected? (code
for other cases notwithstanding). The worksheet has a
variety of other data cells and cell formats.
-----Original Message-----
Dave Ramage basically produced the same code as I and
packaged it all under
the change event which is what you want.

but for my code it would be:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
Select Case Right(LCase(cell.Value), 2)
Case "ot"
cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select
Next

End Sub

Remove any conditional formatting. You can check the
colorindex property
in VBA help for mapping a color to the color index or run
code like this on
a blank sheet

Sub aa_tester11()
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 2).Value = i
Next

End Sub



--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
The code will act when the user makes the entry, and the
cell in which they make the entry is formatted.
-----Original Message-----
When do you want it to happen. Will the user enter one
of those values
(Worksheet_change event). Will they be produced
through
a formula
(Calculate Event).

Select Case Right(lcase(Cell.value),2)
Case "ot"
Cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True Case "ce"
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select


If the Change event, you can do

cell = Target

If the Calculate event

set rng = me.cells.specialcells
(xlFormulas,xlTextValues)
for each cell in rng


Next

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
The conditional formatting limit is three
conditions. I
am using all three, as follows:
formula conditional format
=RIGHT(G38,2)="ot font bold, background lt.
green
=RIGHT(G38,2)="la" font bold, background
lavander
=RIGHT(G38,2)="ls" font bold, background green

These CFs are good for any cell in the worksheet.

I need additional CF formatting on this worksheet,
for
example, I need to add "ce" and "ct". What would the
code
be, and where would I put it?

Thanks, Phil



.



.



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default Need more CF capacity
I see what you mean. Can you recommend a course of
action? Is there a way to duplicate the CF code - and
expand it to allow more than three CFs?
-----Original Message-----
NO.
as written it would change any other cell not containing
one of the values
to no background color and not bold. That was there to
handle the case
where you delete a cell that was formatted - however, if
there are other
cells you want untouched, then you can't have the Case
Else and least not
without further checks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
Select Case Right(LCase(cell.Value), 2)
Case "ot"
cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
' Case Else
' cell.Interior.ColorIndex = xlNone
' cell.Font.Bold = False
End Select
Next

End Sub
--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
Tom, when a user enters, say, ot, as the right-most two
characters, only that cell is formatted according to the
code, correct? Any other cell will be unaffected? (code
for other cases notwithstanding). The worksheet has a
variety of other data cells and cell formats.
-----Original Message-----
Dave Ramage basically produced the same code as I and
packaged it all under
the change event which is what you want.

but for my code it would be:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
Select Case Right(LCase(cell.Value), 2)
Case "ot"
cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select
Next

End Sub

Remove any conditional formatting. You can check the
colorindex property
in VBA help for mapping a color to the color index or
run
code like this on
a blank sheet

Sub aa_tester11()
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 2).Value = i
Next

End Sub



--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
The code will act when the user makes the entry, and
the
cell in which they make the entry is formatted.
-----Original Message-----
When do you want it to happen. Will the user enter
one
of those values
(Worksheet_change event). Will they be produced
through
a formula
(Calculate Event).

Select Case Right(lcase(Cell.value),2)
Case "ot"
Cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True Case "ce"
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select


If the Change event, you can do

cell = Target

If the Calculate event

set rng = me.cells.specialcells
(xlFormulas,xlTextValues)
for each cell in rng


Next

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
The conditional formatting limit is three
conditions. I
am using all three, as follows:
formula conditional format
=RIGHT(G38,2)="ot font bold, background lt.
green
=RIGHT(G38,2)="la" font bold, background
lavander
=RIGHT(G38,2)="ls" font bold, background
green

These CFs are good for any cell in the worksheet.

I need additional CF formatting on this worksheet,
for
example, I need to add "ce" and "ct". What would
the
code
be, and where would I put it?

Thanks, Phil



.



.



.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Seating Capacity al_ba Excel Discussion (Misc queries) 3 March 15th 09 05:54 PM
Does someone know what is the capacity of each workbook? madoarm Excel Worksheet Functions 1 August 2nd 07 10:22 PM
Add column until capacity is met DMS Excel Discussion (Misc queries) 0 January 23rd 07 08:51 PM
Capacity Javster95 Excel Discussion (Misc queries) 2 July 1st 05 06:25 PM
Capacity Assessment NG Excel Discussion (Misc queries) 0 January 21st 05 10:41 PM


All times are GMT +1. The time now is 01:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement