Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

hi
where are you getting the case value?

Regards
FSt1

"Shu of AZ" wrote:

Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

hi again.
also what are you trying to do with this.

regards
FSt1

"Shu of AZ" wrote:

Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

I really apologize for the description but no one has been able to help me
with this and Bob Phillips just wrote to me a couple of days that I lost him
and that I was changing the goal posts. I wrote that I needed to change cell
colors for each Case in 12 cells of a range. I said an Event won't work as
no pressing of enter is possible.

The code I put on this post is a small part of a larger macro that does
analysis of data and eventually places on the first worksheet 'Data' and then
transfers the final analysed data from that data source worksheet to this
worksheet 'R12'. When the data comes in, it is set into 12 cells and from
there is then displayed onto 12 other merged cells using a Vlookup. This is
why an Event does not work as the user cannot press enter to create the
Event. The first cell is O5 which is a merged cell of O5 and O6 and then
continues down until there are 12 merged cells to populate

Here's the kicker, the cells that get populated in the range, get a number
that is 1 thru 12. I need to associate a color, to each of the numbers
gathered from the Vlookup and turn the cells color and the font a color.
Sometimes, there may be blanks. Ex. Beginning with O5, it may be '4', then
O7, may be '8' and so on.

"FSt1" wrote:

hi again.
also what are you trying to do with this.

regards
FSt1

"Shu of AZ" wrote:

Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

from a data sheet to a set of cells on the R12 sheet and then using a Vlookup
the cells are populated to another set of cells on the R12 sheet. These last
cells are what is to be coloured.

"FSt1" wrote:

hi
where are you getting the case value?

Regards
FSt1

"Shu of AZ" wrote:

Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

I have included the macro for you to see. The code that is not working,
Case, is near the end. As you can see there are more sheets involved but I
kept it to just trying to get R12 to function figuring I could expand after
the issue was resolved. R1 thru R12 sheets have to do the same thing. They
are mirrors of each other except for the name of the sheet. Thank you for
looking at this. I had to take out 41000 amount of text to be able to post
so there is the middle missing.


Option Explicit


Public Const WiseGuyVersion = "WiseGuy V.65" 'Input box heading

Sub A_Data1()
'
'Data1 Macro
'Macro recorded 08/07/2006 by User
'
'Keyboard Shortcut: Ctrl+q
'
'MSFT MVP, Windows Server - Networking, 2006
'MSFT MVP, Excel,2007'
'
'****************************************
'Beginning of user definable Const values
'****************************************

Const sourceSheet = "DataSorter" 'Source data sheet
Const R1 = "R1"
Const R2 = "R2"
Const R3 = "R3"
Const R4 = "R4"
Const R5 = "R5"
Const R6 = "R6"
Const R7 = "R7"
Const R8 = "R8"
Const R9 = "R9"
Const R10 = "R10"
Const R11 = "R11"
Const R12 = "R12"
Const firstColumnToCopy = "e" 'next is first column to copy, change to D
or E (or other) as needed
Const lastColumnToCopy = "S" 'next is last column to copy under normal
circumstances
Const KeyColumn = "C" 'note any used columns past lastColumnToCopy
will be copied anyhow,
'but changing it to go on out to known last
column will speed things up
'column on source sheet where the key/group
values are at/in
Const destStart = "q5" 'upper left corner on destination sheets for
starting paste operations
Const landingSpot = "a1" 'cell you want to be chosen on the R1 sheet
when all is done
Const pulledForInjury = 99.99 'value for entries where horse was pulled
for injury'
Const SaddleBlanket = "O5:O43"


'**********************************
'End of user definable Const values
'**********************************
Dim LastRow As Long
Dim lastColumn As Long ' prep for O2K7
Dim destSheet As String
Dim DPRO As Long ' Destination Page Row Offset
Dim SPCO As Long ' Source Page Column Offset
Dim DPCO As Long ' Destination Page Column Offset
Dim currentGroup As Integer
Dim LC As Long
Dim RC As Long
Dim dummyTest As Variant
Dim anySheet As Worksheet
Dim TestRow As Long
Dim Match As Variant
Dim I As Long
Dim InsertRows As Long
Dim CutOffDate As Date
Dim LastRowNumber As Long
Dim DeleteCount As Long
Dim dataOffset As Long
Dim thisSheetName As String
Dim anyRange As String
Dim shortestDistance As Single
Dim longestDistance As Single
Dim lousyFinish As Single
Dim lousyBeyer As Single
Dim raceonelength As String
Dim racetwolength As String
Dim racethreelength As String
Dim racefourlength As String
Dim racefivelength As String
Dim racesixlength As String
Dim racesevenlength As String
Dim raceeightlength As String
Dim raceninelength As String
Dim racetenlength As String
Dim raceelevenlength As String
Dim racetwelvelength As String
Dim Excel2007Flag As Boolean ' added in v11
Dim wb As Variant
Dim csvBook As Variant

'************************************************* ********************************
'get csv sheet that is open and then get date from user if it needs to be
changed,
'default is 9 months prior to current system date
'************************************************* ********************************

For Each wb In Workbooks
If Right(wb.Name, 3) = "csv" Then csvBook = wb.Name
Next
Workbooks(csvBook).Sheets(1).Cells.Copy ActiveWorkbook.ActiveSheet.Cells

'******************************
' get cutoff date to keep
'******************************

CutOffDate = GetCutOffDate()

'******************************
' get shortest distance to keep
'******************************

shortestDistance = GetShortestDistance()

'****************************
'get longest distance to keep
'****************************

longestDistance = GetLongestDistance()

'****************************
'get lowest Beyer to keep
'****************************

lousyBeyer = GetLousyBeyer()

'****************************
'get best finish to keep
'****************************

lousyFinish = GetLousyFinish()

'****************************
'get race one length
'****************************

raceonelength = GetRaceLength(1)

'****************************
'get race two length
'****************************

racetwolength = GetRaceLength(2)

'****************************
'get race three length
'****************************

racethreelength = GetRaceLength(3)

'****************************
'get race four length
'****************************

racefourlength = GetRaceLength(4)

'****************************
'get race five length
'****************************

racefivelength = GetRaceLength(5)

'****************************
'get race six length
'****************************

racesixlength = GetRaceLength(6)

'****************************
'get race seven length
'****************************

racesevenlength = GetRaceLength(7)

'****************************
'get race eight length
'****************************

raceeightlength = GetRaceLength(8)

'****************************
'get race nine length
'****************************

raceninelength = GetRaceLength(9)

'****************************
'get race ten length
'****************************

racetenlength = GetRaceLength(10)

'****************************
'get race eleven length
'****************************

raceelevenlength = GetRaceLength(11)

'****************************
'get race twelve length
'****************************

racetwelvelength = GetRaceLength(12)

' continue uninterrupted
On Error GoTo 0


REMOVED





Worksheets("BetSheet").Select
Range("a1").Select
Worksheets("R12").Select
Range(landingSpot).Select
Worksheets("R11").Select
Range(landingSpot).Select
Worksheets("R10").Select
Range(landingSpot).Select
Worksheets("R9").Select
Range(landingSpot).Select
Worksheets("R8").Select
Range(landingSpot).Select
Worksheets("R7").Select
Range(landingSpot).Select
Worksheets("R6").Select
Range(landingSpot).Select
Worksheets("R5").Select
Range(landingSpot).Select
Worksheets("R4").Select
Range(landingSpot).Select
Worksheets("R3").Select
Range(landingSpot).Select
Worksheets("R2").Select
Range(landingSpot).Select
Worksheets("R1").Select
Range(landingSpot).Select

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

Application.ScreenUpdating = True
Application.Cursor = xlDefault
On Error GoTo 0
End Sub

Private Sub DataReduction(anyColumn As String)
Dim anyRange As String
Dim LastRowNumber As Long
Dim dataOffset As Long
'v10.1a added back
Const CutOffValue = 1
Const FirstDataRow = "2"

'safety valve
If anyColumn = "" Then
Exit Sub
End If

If Val(Left(Application.Version, 2)) 11 Then
If ActiveSheet.Cells(Rows.CountLarge, anyColumn).End(xlUp).Row
LastRowNumber Then
LastRowNumber = ActiveSheet.Cells(Rows.CountLarge,
anyColumn).End(xlUp).Row
End If
Else
'for Excel versions prior to 2007 ("12.0")
If ActiveSheet.Cells(Rows.Count, anyColumn).End(xlUp).Row
LastRowNumber Then
LastRowNumber = ActiveSheet.Cells(Rows.Count,
anyColumn).End(xlUp).Row
End If
End If
anyRange = anyColumn & FirstDataRow
'next safety valve - in case anyColumn had invalid column ID in it
On Error Resume Next
Range(anyRange).Select
If Err < 0 Then
Err.Clear
On Error GoTo 0
Exit Sub ' no alert given
End If
dataOffset = 0 ' belt and suspenders, make sure dataoffset starts at zero
Do Until ActiveCell.Offset(dataOffset, 0) CutOffValue Or
Range(anyRange).Row + dataOffset LastRowNumber
If ActiveCell.Offset(dataOffset, 0) <= CutOffValue Then ' one or less
ActiveCell.Offset(dataOffset, 1).ClearContents ' column to the
right
End If
dataOffset = dataOffset + 1
Loop

End Sub




"FSt1" wrote:

hi again.
also what are you trying to do with this.

regards
FSt1

"Shu of AZ" wrote:

Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

hi
i think your problem is that you are trying to color 12 different cells 12
different colors with 1 select case. not. to do that you will have to have a
loop to loop through your range and run the select case for each cell.
something like this
Dim rng As Range
Set rng = Range("G2:G7")
For Each cell In rng
Select Case cell
Case Is = 1
cell.Interior.ColorIndex = 3
Case Is = 2
cell.Interior.ColorIndex = 6
Case Is = 3
cell.Interior.ColorIndex = 41
End Select
Next cell
tested. works
now it's near my bed time. post back if you have further problems but i may
not pick back up till tomorrow.
Regards
FSt1
"Shu of AZ" wrote:

I really apologize for the description but no one has been able to help me
with this and Bob Phillips just wrote to me a couple of days that I lost him
and that I was changing the goal posts. I wrote that I needed to change cell
colors for each Case in 12 cells of a range. I said an Event won't work as
no pressing of enter is possible.

The code I put on this post is a small part of a larger macro that does
analysis of data and eventually places on the first worksheet 'Data' and then
transfers the final analysed data from that data source worksheet to this
worksheet 'R12'. When the data comes in, it is set into 12 cells and from
there is then displayed onto 12 other merged cells using a Vlookup. This is
why an Event does not work as the user cannot press enter to create the
Event. The first cell is O5 which is a merged cell of O5 and O6 and then
continues down until there are 12 merged cells to populate

Here's the kicker, the cells that get populated in the range, get a number
that is 1 thru 12. I need to associate a color, to each of the numbers
gathered from the Vlookup and turn the cells color and the font a color.
Sometimes, there may be blanks. Ex. Beginning with O5, it may be '4', then
O7, may be '8' and so on.

"FSt1" wrote:

hi again.
also what are you trying to do with this.

regards
FSt1

"Shu of AZ" wrote:

Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

Select Case SaddleBlanket

Hi. I believe "SaddleBlanket" is a Range, but you are treating it as thou it is one cell.
Just guessing, but here's something to try.


Sub Demo()
Dim SaddleBlanket As Range
Dim Cell As Range
Dim Ci As Long 'ColorIndex

Set SaddleBlanket = Range("O5:O43")

For Each Cell In SaddleBlanket.Cells
'Make sure it's an Integer
Select Case CLng(Cell.Value)
Case 1: Ci = 3
Case 2: Ci = 2
Case 3: Ci = 41
Case 4: Ci = 6
Case 5: Ci = 50
Case 6: Ci = 1
Case 7: Ci = 46
Case 8: Ci = 7
Case 9: Ci = 42
Case 10: Ci = 13
Case 11: Ci = 48
Case 12: Ci = 4
End Select
Cell.Interior.ColorIndex = Ci
Next Cell
End Sub

--
HTH :)
Dana DeLouis


"Shu of AZ" wrote in message ...

Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:


hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:


Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

So as in the reply where I show the 'Entire' Macro, how does this fit in. I
noticed End Sub which in my macro already has one. You are right, I was
treating it as a cell but it is a range. Thank you.

"Dana DeLouis" wrote:

Select Case SaddleBlanket

Hi. I believe "SaddleBlanket" is a Range, but you are treating it as thou it is one cell.
Just guessing, but here's something to try.


Sub Demo()
Dim SaddleBlanket As Range
Dim Cell As Range
Dim Ci As Long 'ColorIndex

Set SaddleBlanket = Range("O5:O43")

For Each Cell In SaddleBlanket.Cells
'Make sure it's an Integer
Select Case CLng(Cell.Value)
Case 1: Ci = 3
Case 2: Ci = 2
Case 3: Ci = 41
Case 4: Ci = 6
Case 5: Ci = 50
Case 6: Ci = 1
Case 7: Ci = 46
Case 8: Ci = 7
Case 9: Ci = 42
Case 10: Ci = 13
Case 11: Ci = 48
Case 12: Ci = 4
End Select
Cell.Interior.ColorIndex = Ci
Next Cell
End Sub

--
HTH :)
Dana DeLouis


"Shu of AZ" wrote in message ...
Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

Here's how I applied it to the current code in two places. The cells did
not turn colors when I ran it.

' at the beginning

Dim Excel2007Flag As Boolean ' added in v11
Dim wb As Variant
Dim csvBook As Variant

Dim SaddleBlanket As Range
Dim Cell As Range
Dim Ci As Long 'ColorIndex

Set SaddleBlanket = Range("O5:O43")

' then at the end

For Each Cell In SaddleBlanket.Cells
'Make sure it's an Integer
Select Case CLng(Cell.Value)
Case 1: Ci = 3
Case 2: Ci = 2
Case 3: Ci = 41
Case 4: Ci = 6
Case 5: Ci = 50
Case 6: Ci = 1
Case 7: Ci = 46
Case 8: Ci = 7
Case 9: Ci = 42
Case 10: Ci = 13
Case 11: Ci = 48
Case 12: Ci = 4
End Select
Cell.Interior.ColorIndex = Ci
Next Cell

Application.ScreenUpdating = True
Application.Cursor = xlDefault
On Error GoTo 0
End Sub





"Dana DeLouis" wrote:

Select Case SaddleBlanket

Hi. I believe "SaddleBlanket" is a Range, but you are treating it as thou it is one cell.
Just guessing, but here's something to try.


Sub Demo()
Dim SaddleBlanket As Range
Dim Cell As Range
Dim Ci As Long 'ColorIndex

Set SaddleBlanket = Range("O5:O43")

For Each Cell In SaddleBlanket.Cells
'Make sure it's an Integer
Select Case CLng(Cell.Value)
Case 1: Ci = 3
Case 2: Ci = 2
Case 3: Ci = 41
Case 4: Ci = 6
Case 5: Ci = 50
Case 6: Ci = 1
Case 7: Ci = 46
Case 8: Ci = 7
Case 9: Ci = 42
Case 10: Ci = 13
Case 11: Ci = 48
Case 12: Ci = 4
End Select
Cell.Interior.ColorIndex = Ci
Next Cell
End Sub

--
HTH :)
Dana DeLouis


"Shu of AZ" wrote in message ...
Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

Instead of applying it to your current code, how about making a standalone
procedure.

Then put some test data in a test worksheet (in o5:o43) and run the code.

Shu of AZ wrote:

Here's how I applied it to the current code in two places. The cells did
not turn colors when I ran it.

' at the beginning

Dim Excel2007Flag As Boolean ' added in v11
Dim wb As Variant
Dim csvBook As Variant

Dim SaddleBlanket As Range
Dim Cell As Range
Dim Ci As Long 'ColorIndex

Set SaddleBlanket = Range("O5:O43")

' then at the end

For Each Cell In SaddleBlanket.Cells
'Make sure it's an Integer
Select Case CLng(Cell.Value)
Case 1: Ci = 3
Case 2: Ci = 2
Case 3: Ci = 41
Case 4: Ci = 6
Case 5: Ci = 50
Case 6: Ci = 1
Case 7: Ci = 46
Case 8: Ci = 7
Case 9: Ci = 42
Case 10: Ci = 13
Case 11: Ci = 48
Case 12: Ci = 4
End Select
Cell.Interior.ColorIndex = Ci
Next Cell

Application.ScreenUpdating = True
Application.Cursor = xlDefault
On Error GoTo 0
End Sub

"Dana DeLouis" wrote:

Select Case SaddleBlanket

Hi. I believe "SaddleBlanket" is a Range, but you are treating it as thou it is one cell.
Just guessing, but here's something to try.


Sub Demo()
Dim SaddleBlanket As Range
Dim Cell As Range
Dim Ci As Long 'ColorIndex

Set SaddleBlanket = Range("O5:O43")

For Each Cell In SaddleBlanket.Cells
'Make sure it's an Integer
Select Case CLng(Cell.Value)
Case 1: Ci = 3
Case 2: Ci = 2
Case 3: Ci = 41
Case 4: Ci = 6
Case 5: Ci = 50
Case 6: Ci = 1
Case 7: Ci = 46
Case 8: Ci = 7
Case 9: Ci = 42
Case 10: Ci = 13
Case 11: Ci = 48
Case 12: Ci = 4
End Select
Cell.Interior.ColorIndex = Ci
Next Cell
End Sub

--
HTH :)
Dana DeLouis


"Shu of AZ" wrote in message ...
Did not change any results, see below,

Worksheets(R12).Select

Select Case SaddleBlanket
Case Is = 1
Selection.Interior.ColorIndex = 3
Case Is = 2
Selection.Interior.ColorIndex = 2
Case Is = 3
Selection.Interior.ColorIndex = 41
Case Is = 4
Selection.Interior.ColorIndex = 6
Case Is = 5
Selection.Interior.ColorIndex = 50
Case Is = 6
Selection.Interior.ColorIndex = 1
Case Is = 7
Selection.Interior.ColorIndex = 46
Case Is = 8
Selection.Interior.ColorIndex = 7
Case Is = 9
Selection.Interior.ColorIndex = 42
Case Is = 10
Selection.Interior.ColorIndex = 13
Case Is = 11
Selection.Interior.ColorIndex = 48
Case Is = 12
Selection.Interior.ColorIndex = 4
End Select

"FSt1" wrote:

hi
i've never seen it done quite like this so i'll offer a suggestion.
Worksheets(R12).Select
'Selection.Interior 'remove

Select Case SaddleBlanket
Case Is = 1
Selection.interior.ColorIndex = 3
Case Is = 2
Selection.interior.ColorIndex = 2
Case Is = 3
ect...ect

Regards
FSt1

"Shu of AZ" wrote:

Could you look at the below formula for Select Case and tell me what is
incorrect. It does not perform correctly on the worksheet.

Const SaddleBlanket = "O5:O43"
Dim SaddleBlanket As String

Worksheets(R12).Select
Range(SaddleBlanket).Select
Selection.Interior

Select Case SaddleBlanket
Case Is = 1
Selection.ColorIndex = 3
Case Is = 2
Selection.ColorIndex = 2
Case Is = 3
Selection.ColorIndex = 41
Case Is = 4
Selection.ColorIndex = 6
Case Is = 5
Selection.ColorIndex = 50
Case Is = 6
Selection.ColorIndex = 1
Case Is = 7
Selection.ColorIndex = 46
Case Is = 8
Selection.ColorIndex = 7
Case Is = 9
Selection.ColorIndex = 42
Case Is = 10
Selection.ColorIndex = 13
Case Is = 11
Selection.ColorIndex = 48
Case Is = 12
Selection.ColorIndex = 4
End Select

--

Dave Peterson

Hi. I can't really tell from this why it didn't work. It worked ok in my simple test data.

As a side note, a program that I have suggested the following instead:

Sub Demo()
Dim SaddleBlanket As Range
Dim Cell As Range
Dim K As Long
Dim Ci As Long 'ColorIndex

Set SaddleBlanket = Range("O5:O43")

For Each Cell In SaddleBlanket.Cells
Ci = xlColorIndexNone 'a default value
'Make sure it's an Integer
K = CLng(Cell.Value)
Select Case K
Case 1 To 5, 12: Ci = 695945850 Mod (K + 50)
Case 6 To 11: Ci = 788727913 Mod (K + 50)
End Select
Cell.Interior.ColorIndex = Ci
Next Cell
End Sub

--
HTH :)
Dana DeLouis

<< snip

Ano of course, there is this option as well:

Sub Demo()
Dim SaddleBlanket As Range
Dim Cell As Range
Dim K As Long
Dim m

m = Array(3, 2, 41, 6, 50, 1, 46, 7, 42, 13, 48, 4)
Set SaddleBlanket = Range("O5:O43")

For Each Cell In SaddleBlanket.Cells
'Make sure it's an Integer
K = CLng(Cell.Value)
Select Case K
Case 1 To 12
Cell.Interior.ColorIndex = WorksheetFunction.Index(m, K)
Case Else
Cell.Interior.ColorIndex = xlColorIndexNone
End Select
Next Cell
End Sub

--
Dana DeLouis