2 x Case // Error why ?

[Philipp Oberleitner]

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

 

[JE McGimpsey]

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

 

[Philipp Oberleitner]

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

Thanks alot+

 

[JE McGimpsey]

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

 

[Dana DeLouis]

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

 

[Dana DeLouis]

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

 

[JE McGimpsey]

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.

 

[Tom Ogilvy]

I already gave you code to do this when you asked about sorting on these
colors.