Help with case statement

[austenr]

Want to test a range of cells in col A and bold the entire row if it is true. Can you use the case statement to accomplish this?

[austenr]

can anyone show me a better way to accomplish this?


Sub BoldTotalLines()
For Each cell In Range("A1").CurrentRegion.Resize(, 1)
If Left(cell.Value, 5) = "EMP" Then
cell.Resize(1, 8).Font.Bold = True
Else
If Left(cell.Value, 5) = "FAM" Then
cell.Resize(1, 8).Font.Bold = True
Else
If Left(cell.Value, 5) = "CHI" Then
cell.Resize(1, 8).Font.Bold = True
Else
If Left(cell.Value, 5) = "+------" Then
cell.Resize(1, 8).Font.Bold = True
End If
End If
End If
End If
Next cell
End Sub

[Bob Phillips]

can anyone show me a better way to accomplish this?

Better? I don't know, but shorter



Sub BoldTotalLines()
Dim cell As Range
For Each cell In Range("A1").CurrentRegion.Resize(, 1)
Select Case Left(cell.Value, 5)
Case "EMP", "FAM", "CHI", "+------":
cell.Resize(1, 8).Font.Bold = True
End Select
Next cell
End Sub

[austenr]

good enough...just wanted to shorten it. Can you use this same approach to do a range of cells and not just one?

[Bob Phillips]

good enough...just wanted to shorten it. Can you use this same approach to do a range of cells and not just one?

Can you clarify what you mean?

[austenr]

Currently it bolds one cell. Can you bold say from A1:A3?

[austenr]

Sorry...scratch that...what I meant to say is how do you look for say cell C3 and bold it and the two previous cells?

[Bob Phillips]

Sorry...scratch that...what I meant to say is how do you look for say cell C3 and bold it and the two previous cells?

Previous rows


Range("C3").Offset(-2,0).Resize(3,1)

Previous columns


Range("C3").Offset(0,-2).Resize(1,3)

[austenr]

Could you incorporate that into your code? Also is there a way to navigate around a blank row?

[Bob Phillips]

Could you incorporate that intoi your code?


Sub BoldTotalLines()
Dim cell As Range
For Each cell In Range("A1").CurrentRegion.Resize(, 1)
Select Case Left(cell.Value, 5)
Case "EMP", "FAM", "CHI", "+------":
cell.Offset(-2, 0).Resize(3, 1).Font.Bold = True
' or alternatively for columns
' cell.Offset(0,-2).Resize(1,3).Font.Bold = True
End Select
Next cell
End Sub


But this could give a problem if anything in row 1 or 2 (or columns 1 or 2 depending on the version you use) matches, as there are no 2 previous in this instance.


also is there a way to navigate around a blank row?

Doesn't it effectively do that by testing for a designated value?

[austenr]

no. it stops and does not do anything.

[austenr]

try it with the sample data attached. all cells should be bold.

[Bob Phillips]

no. it stops and does not do anything.

Ah. That is probably because you are using CurrentRegion, which just goes to the first blank.

Try


Sub BoldTotalLines()
Dim cell As Range
For Each cell In Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row)
Select Case Left(cell.Value, 5)
Case "EMP", "FAM", "CHI", "+------":
cell.Offset(-2, 0).Resize(3, 1).Font.Bold = True
' or alternatively for columns
' cell.Offset(0,-2).Resize(1,3).Font.Bold = True
End Select
Next cell
End Sub

[austenr]

Thanks. That got it. I always have trouble with the blank rows and sometimes the offset. That worked perfectly. Thanks Solved

[Norie]

austenr

Could you not use Conditional Formatting for this?

[austenr]

perhaps..however i look at all of this as a learning experience. these macros force you to think not just use something already there.

[xCav8r]

I would have done the same as xld here, but since you seem to be asking for the purpose of learning, you could have simplified your If...then...end if statement considerably, though it would not have been as easily read and understood as Select Case. So, for learning purposes alone, I give you... ;)

...the If equivalent:



strValueToCheck = Left(Cell.Value, 5)
If strValueToCheck = "EMP" Or _
strValueToCheck = "FAM" Or _
strValueToCheck = "CHI" Or _
strValueToCheck = "+------" Then
Cell.Resize(1, 8).Font.Bold = True
End If


Generally speaking, you only want to embed conditional statements when the embedded conditional statement is necessarily dependent on the one in which it is embedded.