f2e4
05-23-2008, 02:22 AM
Hey people,
I am trying to write some code that will find a value in a selected column, and once it is found, do some formatting on that row.
The steps are meant to be as follows:
Find last active row - This bit works fine
Starting in Row 3 Column 2, search down until cell value is "Leadership & Management Total"
Once value is found, select that cell and then select over to the last active column in that row.
The problem:
I had a previous Do Loop Until code that worked fine but i had to modify it as the headings I was looking for were not always there (depending on data). This threw up an error as the loop kept on going until the end of the sheet.
This code is meant to be conditional - i.e. run the code, find the value, if value is there, do teh formatting bit, if value is not there, exit do loop.
a = 3
Do While a <= lastactiverow
If Cells(a, 2) = "Leadership & Management Total" Then
Cells(a, 2).Select
'You can ignore this formatting bit, as i know this works
'Range(Selection, Cells(a, LastCol)).Select
'With Selection.Interior
'.ColorIndex = 15
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
'End With
'Selection.Font.ColorIndex = 2
'With Selection
'.HorizontalAlignment = xlLeft
'.VerticalAlignment = xlBottom
'.WrapText = False
'.Orientation = 0
'.AddIndent = False
'.IndentLevel = 0
'.ShrinkToFit = False
'.ReadingOrder = xlContext
'.MergeCells = False
'End With
Else
Exit Do
End If
a = a + 1
Loop
Hope someone can sort this out
And as always, thanks for the help
I am trying to write some code that will find a value in a selected column, and once it is found, do some formatting on that row.
The steps are meant to be as follows:
Find last active row - This bit works fine
Starting in Row 3 Column 2, search down until cell value is "Leadership & Management Total"
Once value is found, select that cell and then select over to the last active column in that row.
The problem:
I had a previous Do Loop Until code that worked fine but i had to modify it as the headings I was looking for were not always there (depending on data). This threw up an error as the loop kept on going until the end of the sheet.
This code is meant to be conditional - i.e. run the code, find the value, if value is there, do teh formatting bit, if value is not there, exit do loop.
a = 3
Do While a <= lastactiverow
If Cells(a, 2) = "Leadership & Management Total" Then
Cells(a, 2).Select
'You can ignore this formatting bit, as i know this works
'Range(Selection, Cells(a, LastCol)).Select
'With Selection.Interior
'.ColorIndex = 15
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
'End With
'Selection.Font.ColorIndex = 2
'With Selection
'.HorizontalAlignment = xlLeft
'.VerticalAlignment = xlBottom
'.WrapText = False
'.Orientation = 0
'.AddIndent = False
'.IndentLevel = 0
'.ShrinkToFit = False
'.ReadingOrder = xlContext
'.MergeCells = False
'End With
Else
Exit Do
End If
a = a + 1
Loop
Hope someone can sort this out
And as always, thanks for the help