String Handling Discrepancy 2003 vs. 2007

  • Thread starter Thread starter Brian B
  • Start date Start date
B

Brian B

Hello,
I was able to run a macro perfectly well using Strings that included spaces
and dashes using the following code within Excel 2007, but not within Excel
2003. In Excel 2003 the following code only worked for Single character
strings with no spaces, dashes, etc. Is there an easy change of the
following code to make it work for a string such as "MD - Not Started", i.e.,
with spaces, so it will work in 2003 as well. This is a surprising and
strange discrepancy between the two versions.

[
Sub Final_colorcode_norecord()

'Variables for counting through cells
Dim RowCount As Integer
Dim ColumnCount As Integer

Dim RowMax As Integer
Dim ColMax As Integer

Dim colorRed As String
Dim colorYellow As String
Dim colorGreen As String

'find the max row and col of the active worksheet
RowMax = ActiveSheet.UsedRange.Rows.Count
ColMax = ActiveSheet.UsedRange.Columns.Count

'Ask for text to account for
colorRed = InputBox("Enter the string of text that will result in a RED
Cell Coloring:", "Red Coloring")
colorYellow = InputBox("Enter the string of text that will result in a
YELLOW Cell Coloring:", "Yellow Coloring")
colorGreen = InputBox("Enter the string of text that will result in a
GREEN Cell Coloring:", "Green Coloring")
'nested for loops for counting through 2d cell grid

For RowCount = 1 To RowMax
For ColumnCount = 1 To ColMax


'check cell text for color red string
If Cells(RowCount, ColumnCount).Text = colorRed Then
Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 0, 0) 'Red
End If

'check cell text for color yellow string
If Cells(RowCount, ColumnCount).Text = colorYellow Then
Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 255, 0) 'Yellow
End If

'check cell text for color green string
If Cells(RowCount, ColumnCount).Text = colorGreen Then
Cells(RowCount, ColumnCount).Interior.Color = RGB(0, 255, 0) 'Green
End If

Next ColumnCount
Next RowCount

End Sub
]
TIA,
Brian
 
Brian-

I pasted your code into XL2003, and it works perfectly on cells that have
spaces and dashes. Things to check:

1. Do you want the code to be case sensitive? If not, take both the input
string and the cell text and compare them both as ucase or lcase, e.g. if
lcase(colorRed)= lcase(cell.text) then...

2. Where does your data come from? If it is a report (or a scraped HTML
file), then the spaces may not be chr(32), even though it looks the same.
Dashes might be the short dash or long dash, etc. Try using asc("<value>") on
any possible problem areas to see if they are really the character they
appear to be.

HTH,
Keith
 
Short dash and long dash are 45 and 150, respectively.

ker_01 said:
Brian-

I pasted your code into XL2003, and it works perfectly on cells that have
spaces and dashes. Things to check:

1. Do you want the code to be case sensitive? If not, take both the input
string and the cell text and compare them both as ucase or lcase, e.g. if
lcase(colorRed)= lcase(cell.text) then...

2. Where does your data come from? If it is a report (or a scraped HTML
file), then the spaces may not be chr(32), even though it looks the same.
Dashes might be the short dash or long dash, etc. Try using asc("<value>") on
any possible problem areas to see if they are really the character they
appear to be.

HTH,
Keith

Brian B said:
Hello,
I was able to run a macro perfectly well using Strings that included spaces
and dashes using the following code within Excel 2007, but not within Excel
2003. In Excel 2003 the following code only worked for Single character
strings with no spaces, dashes, etc. Is there an easy change of the
following code to make it work for a string such as "MD - Not Started", i.e.,
with spaces, so it will work in 2003 as well. This is a surprising and
strange discrepancy between the two versions.

[
Sub Final_colorcode_norecord()

'Variables for counting through cells
Dim RowCount As Integer
Dim ColumnCount As Integer

Dim RowMax As Integer
Dim ColMax As Integer

Dim colorRed As String
Dim colorYellow As String
Dim colorGreen As String

'find the max row and col of the active worksheet
RowMax = ActiveSheet.UsedRange.Rows.Count
ColMax = ActiveSheet.UsedRange.Columns.Count

'Ask for text to account for
colorRed = InputBox("Enter the string of text that will result in a RED
Cell Coloring:", "Red Coloring")
colorYellow = InputBox("Enter the string of text that will result in a
YELLOW Cell Coloring:", "Yellow Coloring")
colorGreen = InputBox("Enter the string of text that will result in a
GREEN Cell Coloring:", "Green Coloring")
'nested for loops for counting through 2d cell grid

For RowCount = 1 To RowMax
For ColumnCount = 1 To ColMax


'check cell text for color red string
If Cells(RowCount, ColumnCount).Text = colorRed Then
Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 0, 0) 'Red
End If

'check cell text for color yellow string
If Cells(RowCount, ColumnCount).Text = colorYellow Then
Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 255, 0) 'Yellow
End If

'check cell text for color green string
If Cells(RowCount, ColumnCount).Text = colorGreen Then
Cells(RowCount, ColumnCount).Interior.Color = RGB(0, 255, 0) 'Green
End If

Next ColumnCount
Next RowCount

End Sub
]
TIA,
Brian
Click to expand...
Click to expand...
 
Interesting. It turns out there's no discrepancy between the two versions.
It's just that in the '07 spreadsheet the cell contained only the Searched
String. the '03 cell would contain both the searched string and another
string.

What it boils down to is I have to use the InStr function effectively. I'm
currently having trouble trying to convert "Cells(RowCount,
ColumnCount).Text" into a String so as to be able to evaluate it against the
"colorRed" string, etc. using the Instr function. It appears to be a bit
difficult.
Thanks for your assistance,
Brian

ker_01 said:
Short dash and long dash are 45 and 150, respectively.

ker_01 said:
Brian-

I pasted your code into XL2003, and it works perfectly on cells that have
spaces and dashes. Things to check:

1. Do you want the code to be case sensitive? If not, take both the input
string and the cell text and compare them both as ucase or lcase, e.g. if
lcase(colorRed)= lcase(cell.text) then...

2. Where does your data come from? If it is a report (or a scraped HTML
file), then the spaces may not be chr(32), even though it looks the same.
Dashes might be the short dash or long dash, etc. Try using asc("<value>") on
any possible problem areas to see if they are really the character they
appear to be.

HTH,
Keith

Brian B said:
Hello,
I was able to run a macro perfectly well using Strings that included spaces
and dashes using the following code within Excel 2007, but not within Excel
2003. In Excel 2003 the following code only worked for Single character
strings with no spaces, dashes, etc. Is there an easy change of the
following code to make it work for a string such as "MD - Not Started", i.e.,
with spaces, so it will work in 2003 as well. This is a surprising and
strange discrepancy between the two versions.

[
Sub Final_colorcode_norecord()

'Variables for counting through cells
Dim RowCount As Integer
Dim ColumnCount As Integer

Dim RowMax As Integer
Dim ColMax As Integer

Dim colorRed As String
Dim colorYellow As String
Dim colorGreen As String

'find the max row and col of the active worksheet
RowMax = ActiveSheet.UsedRange.Rows.Count
ColMax = ActiveSheet.UsedRange.Columns.Count

'Ask for text to account for
colorRed = InputBox("Enter the string of text that will result in a RED
Cell Coloring:", "Red Coloring")
colorYellow = InputBox("Enter the string of text that will result in a
YELLOW Cell Coloring:", "Yellow Coloring")
colorGreen = InputBox("Enter the string of text that will result in a
GREEN Cell Coloring:", "Green Coloring")
'nested for loops for counting through 2d cell grid

For RowCount = 1 To RowMax
For ColumnCount = 1 To ColMax


'check cell text for color red string
If Cells(RowCount, ColumnCount).Text = colorRed Then
Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 0, 0) 'Red
End If

'check cell text for color yellow string
If Cells(RowCount, ColumnCount).Text = colorYellow Then
Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 255, 0) 'Yellow
End If

'check cell text for color green string
If Cells(RowCount, ColumnCount).Text = colorGreen Then
Cells(RowCount, ColumnCount).Interior.Color = RGB(0, 255, 0) 'Green
End If

Next ColumnCount
Next RowCount

End Sub
]
TIA,
Brian
Click to expand...
Click to expand...
Click to expand...
 
Back
Top