Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > 2 x Case // Error why ?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default 2 x Case // Error why ?
For x = 1 To Blatt.Cells(Rows.Count, "D").End(xlUp).Row
Select Case Blatt.Cells(x, "C").Value
Case "critical"
Select Case Blatt.Cells(i, "K").Color
Case RGB(128, 255, 196)
xAnz = xAnz + 1
End Select
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select
Next x


Cant i use . Color with this context to count lines with a certain rgb
background color ?


Thx alot


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,624
Default 2 x Case // Error why ?
Couple of problems here.

First, Cells/Range doesn't have a Color property. For background color,
you need to use

Select Case Blatt.Cells(i, "K").Interior.Color

Second, XL can only display 56 colors at a time, so unless the RGB value
is one of the 56 colors in the color palette, it won't match. When you
assign an RGB value, XL tries to find the closest match. For instance,
when I enter this in the immediate window with the default palette:

Cells(1,"K").interior.Color = RGB(128,255,196)
?RGB(128,255,196)
12910464
?cells(1,"K").interior.color
13434828
?cells(1,"K").interior.color=RGB(128,255,196)
False

You're usually better off to use the .ColorIndex property.

third, Select Case is an 'expensive' control structure compared to
If...Then. It's probably appropriate for the outer Select Case
structure, but the inner one would be better off using If...Then:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
If Blatt.Cells(i, "K").Interior.ColorIndex = 35 Then _
xAnz = xAnz + 1
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select

Or you could use the fact that VBA's True evaluates to -1:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
xAnz = xAnz - (Blatt.Cells(i, "K").Interior.ColorIndex = 35)
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select


In article ,
"Philipp Oberleitner" wrote:

For x = 1 To Blatt.Cells(Rows.Count, "D").End(xlUp).Row
Select Case Blatt.Cells(x, "C").Value
Case "critical"
Select Case Blatt.Cells(i, "K").Color
Case RGB(128, 255, 196)
xAnz = xAnz + 1
End Select
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select
Next x


Cant i use . Color with this context to count lines with a certain rgb
background color ?


Thx alot
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default 2 x Case // Error why ?
How can i see what colorindex to use which is nearest to my rgb RGB(255,
128, 128) ß

Thanks alot+


"JE McGimpsey" wrote in message
...
Couple of problems here.

First, Cells/Range doesn't have a Color property. For background color,
you need to use

Select Case Blatt.Cells(i, "K").Interior.Color

Second, XL can only display 56 colors at a time, so unless the RGB value
is one of the 56 colors in the color palette, it won't match. When you
assign an RGB value, XL tries to find the closest match. For instance,
when I enter this in the immediate window with the default palette:

Cells(1,"K").interior.Color = RGB(128,255,196)
?RGB(128,255,196)
12910464
?cells(1,"K").interior.color
13434828
?cells(1,"K").interior.color=RGB(128,255,196)
False

You're usually better off to use the .ColorIndex property.

third, Select Case is an 'expensive' control structure compared to
If...Then. It's probably appropriate for the outer Select Case
structure, but the inner one would be better off using If...Then:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
If Blatt.Cells(i, "K").Interior.ColorIndex = 35 Then _
xAnz = xAnz + 1
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select

Or you could use the fact that VBA's True evaluates to -1:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
xAnz = xAnz - (Blatt.Cells(i, "K").Interior.ColorIndex = 35)
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select


In article ,
"Philipp Oberleitner" wrote:

For x = 1 To Blatt.Cells(Rows.Count, "D").End(xlUp).Row
Select Case Blatt.Cells(x, "C").Value
Case "critical"
Select Case Blatt.Cells(i, "K").Color
Case RGB(128, 255, 196)
xAnz = xAnz + 1
End Select
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select
Next x


Cant i use . Color with this context to count lines with a certain rgb
background color ?


Thx alot


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,624
Default 2 x Case // Error why ?
One way:

Format a cell with RGB, then read the colorindex:

Dim nMyColorIndex As Long
Dim nOldColorIndex As Long
With Range("IV1").Interior
nOldColorIndex = .ColorIndex
.Color = RGB(255, 128, 128)
nMyColorIndex = .ColorIndex
.ColorIndex = nOldColorIndex
End With



In article ,
"Philipp Oberleitner" wrote:

How can i see what colorindex to use which is nearest to my rgb RGB(255,
128, 128) ß

Thanks alot+


"JE McGimpsey" wrote in message
...
Couple of problems here.

First, Cells/Range doesn't have a Color property. For background color,
you need to use

Select Case Blatt.Cells(i, "K").Interior.Color

Second, XL can only display 56 colors at a time, so unless the RGB value
is one of the 56 colors in the color palette, it won't match. When you
assign an RGB value, XL tries to find the closest match. For instance,
when I enter this in the immediate window with the default palette:

Cells(1,"K").interior.Color = RGB(128,255,196)
?RGB(128,255,196)
12910464
?cells(1,"K").interior.color
13434828
?cells(1,"K").interior.color=RGB(128,255,196)
False
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 690
Default 2 x Case // Error why ?
Here's an idea based on JE suggestion. If you look down the 4th Column, the
value of 0 should correspond to what Excel considered the "closest" color.

Sub Demo()
Dim r As Long
Dim MyColor

MyColor = RGB(255, 128, 128)

' See what color it is:
Cells(1, 5).Interior.Color = MyColor

' Look for 0 in 4th Column
For r = 1 To 56
Cells(r, 1) = r
Cells(r, 2).Interior.ColorIndex = r
Cells(r, 3) = Cells(r, 2).Interior.Color
Cells(r, 4) = Abs(Cells(r, 2).Interior.Color - MyColor)
Next r
End Sub

HTH
Dana DeLouis


"Philipp Oberleitner" wrote in message
...
How can i see what colorindex to use which is nearest to my rgb RGB(255,
128, 128) ß

Thanks alot+


"JE McGimpsey" wrote in message
...
Couple of problems here.

First, Cells/Range doesn't have a Color property. For background color,
you need to use

Select Case Blatt.Cells(i, "K").Interior.Color

Second, XL can only display 56 colors at a time, so unless the RGB value
is one of the 56 colors in the color palette, it won't match. When you
assign an RGB value, XL tries to find the closest match. For instance,
when I enter this in the immediate window with the default palette:

Cells(1,"K").interior.Color = RGB(128,255,196)
?RGB(128,255,196)
12910464
?cells(1,"K").interior.color
13434828
?cells(1,"K").interior.color=RGB(128,255,196)
False

You're usually better off to use the .ColorIndex property.

third, Select Case is an 'expensive' control structure compared to
If...Then. It's probably appropriate for the outer Select Case
structure, but the inner one would be better off using If...Then:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
If Blatt.Cells(i, "K").Interior.ColorIndex = 35 Then _
xAnz = xAnz + 1
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select

Or you could use the fact that VBA's True evaluates to -1:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
xAnz = xAnz - (Blatt.Cells(i, "K").Interior.ColorIndex =
35)
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select


In article ,
"Philipp Oberleitner" wrote:

For x = 1 To Blatt.Cells(Rows.Count, "D").End(xlUp).Row
Select Case Blatt.Cells(x, "C").Value
Case "critical"
Select Case Blatt.Cells(i, "K").Color
Case RGB(128, 255, 196)
xAnz = xAnz + 1
End Select
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select
Next x


Cant i use . Color with this context to count lines with a certain rgb
background color ?


Thx alot




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 690
Default 2 x Case // Error why ?
One option could be to change one of the index colors to your desired color.
Then, you can test for that "ColorIndex." Not sure if this is what you have
done.

ActiveWorkbook.Colors(56) = RGB(255, 128, 128)
Cells(1, 6).Interior.ColorIndex = 56

Dana


"Dana DeLouis" wrote in message
...
Here's an idea based on JE suggestion. If you look down the 4th Column,
the
value of 0 should correspond to what Excel considered the "closest" color.

Sub Demo()
Dim r As Long
Dim MyColor

MyColor = RGB(255, 128, 128)

' See what color it is:
Cells(1, 5).Interior.Color = MyColor

' Look for 0 in 4th Column
For r = 1 To 56
Cells(r, 1) = r
Cells(r, 2).Interior.ColorIndex = r
Cells(r, 3) = Cells(r, 2).Interior.Color
Cells(r, 4) = Abs(Cells(r, 2).Interior.Color - MyColor)
Next r
End Sub

HTH
Dana DeLouis


"Philipp Oberleitner" wrote in message
...
How can i see what colorindex to use which is nearest to my rgb RGB(255,
128, 128) ß

Thanks alot+


"JE McGimpsey" wrote in message
...
Couple of problems here.

First, Cells/Range doesn't have a Color property. For background
color,
you need to use

Select Case Blatt.Cells(i, "K").Interior.Color

Second, XL can only display 56 colors at a time, so unless the RGB
value
is one of the 56 colors in the color palette, it won't match. When you
assign an RGB value, XL tries to find the closest match. For instance,
when I enter this in the immediate window with the default palette:

Cells(1,"K").interior.Color = RGB(128,255,196)
?RGB(128,255,196)
12910464
?cells(1,"K").interior.color
13434828
?cells(1,"K").interior.color=RGB(128,255,196)
False

You're usually better off to use the .ColorIndex property.

third, Select Case is an 'expensive' control structure compared to
If...Then. It's probably appropriate for the outer Select Case
structure, but the inner one would be better off using If...Then:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
If Blatt.Cells(i, "K").Interior.ColorIndex = 35 Then _
xAnz = xAnz + 1
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select

Or you could use the fact that VBA's True evaluates to -1:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
xAnz = xAnz - (Blatt.Cells(i, "K").Interior.ColorIndex =
35)
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select


In article ,
"Philipp Oberleitner" wrote:

For x = 1 To Blatt.Cells(Rows.Count, "D").End(xlUp).Row
Select Case Blatt.Cells(x, "C").Value
Case "critical"
Select Case Blatt.Cells(i, "K").Color
Case RGB(128, 255, 196)
xAnz = xAnz + 1
End Select
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select
Next x


Cant i use . Color with this context to count lines with a certain
rgb
background color ?


Thx alot






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,624
Default 2 x Case // Error why ?
If you follow Dana's excellent suggestion, I'd recommend that you put the

ActiveWorkbook.Colors(56) = RGB(255, 128, 128)

code in the Workbook_Open() or other event macro. It's just too easy for
the user to reset the palette, breaking your code.


In article ,
"Dana DeLouis" wrote:

One option could be to change one of the index colors to your desired color.
Then, you can test for that "ColorIndex." Not sure if this is what you have
done.

ActiveWorkbook.Colors(56) = RGB(255, 128, 128)
Cells(1, 6).Interior.ColorIndex = 56
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 2 x Case // Error why ?
I already gave you code to do this when you asked about sorting on these
colors.

--
Regards,
Tom Ogilvy


"Philipp Oberleitner" wrote in message
...
How can i see what colorindex to use which is nearest to my rgb RGB(255,
128, 128) ß

Thanks alot+


"JE McGimpsey" wrote in message
...
Couple of problems here.

First, Cells/Range doesn't have a Color property. For background color,
you need to use

Select Case Blatt.Cells(i, "K").Interior.Color

Second, XL can only display 56 colors at a time, so unless the RGB value
is one of the 56 colors in the color palette, it won't match. When you
assign an RGB value, XL tries to find the closest match. For instance,
when I enter this in the immediate window with the default palette:

Cells(1,"K").interior.Color = RGB(128,255,196)
?RGB(128,255,196)
12910464
?cells(1,"K").interior.color
13434828
?cells(1,"K").interior.color=RGB(128,255,196)
False

You're usually better off to use the .ColorIndex property.

third, Select Case is an 'expensive' control structure compared to
If...Then. It's probably appropriate for the outer Select Case
structure, but the inner one would be better off using If...Then:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
If Blatt.Cells(i, "K").Interior.ColorIndex = 35 Then _
xAnz = xAnz + 1
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select

Or you could use the fact that VBA's True evaluates to -1:

Select Case Blatt.Cells(x, "C").Value
Case "critical"
xAnz = xAnz - (Blatt.Cells(i, "K").Interior.ColorIndex =
35)
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select


In article ,
"Philipp Oberleitner" wrote:

For x = 1 To Blatt.Cells(Rows.Count, "D").End(xlUp).Row
Select Case Blatt.Cells(x, "C").Value
Case "critical"
Select Case Blatt.Cells(i, "K").Color
Case RGB(128, 255, 196)
xAnz = xAnz + 1
End Select
Case "major"
yAnz = yAnz + 1
Case "minor"
zAnz = zAnz + 1
End Select
Next x


Cant i use . Color with this context to count lines with a certain rgb
background color ?


Thx alot




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
change data of entire column from small case to upper case Ann Excel Worksheet Functions 1 August 16th 08 01:06 PM
Case without Select Case error problem Ayo Excel Discussion (Misc queries) 2 May 16th 08 03:48 PM
Change the text from lower case to upper case in an Excel work boo dave01968 Excel Discussion (Misc queries) 2 December 9th 05 09:09 AM
Case Statement error David Adamson[_3_] Excel Programming 2 June 23rd 04 05:01 AM
Hopefully simple Select Case error Kobayashi[_15_] Excel Programming 3 October 3rd 03 07:46 PM


All times are GMT +1. The time now is 01:33 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