If statement based on cell formatting/style

  • Thread starter Thread starter Steven North
  • Start date Start date
S

Steven North

Hi,

I am wondering if it is possible to do the following.
I have an Excel sheet that is some sort of financial report.
I want to have a function that would go...

If CellFormat = Style1 then "H1"
If CellFormat = Style2 then "H2"
If CellFormat = Style3 then "H3"
If CellFormat = Style4 then "H3"
If CellValue = Total then "TL"
Else the line is "LN"

I'd then extend it to... if LN and (Row value = 0) then hide row.

So it would hide rows if the 'resource code' is 0

The other option is to do it manually but was wondering if it's possible to do it via VBA.
 
Steven said:
I am wondering if it is possible to do the following.
I have an Excel sheet that is some sort of financial report.
I want to have a function that would go...

If CellFormat = Style1 then "H1"
If CellFormat = Style2 then "H2"
If CellFormat = Style3 then "H3"
If CellFormat = Style4 then "H3"
If CellValue = Total then "TL"
Else the line is "LN"

I'd then extend it to... if LN and (Row value = 0) then hide row.

So it would hide rows if the 'resource code' is 0

The other option is to do it manually but was wondering if it's possible
to do it via VBA.
Click to expand...

Something like this, perhaps? This assumes you're using Styles; if you're
using individual elements like bolding, fonts, and/or colors, you'll need to
essentially completely rewrite the entire function...

Function blargh(what As Range)
Select Case what.Style
Case Style1
blargh = "H1"
Case Style2
blargh = "H2"
Case Style3
blargh = "H3"
Case Style4
blargh = "H4"
Case Else
If what.Value = "Total" Then
blargh = "TL"
Else
If Application.WorksheetFunction.Sum(what.EntireRow) = 0 _
Then what.EntireRow.Hidden = True
blargh = "LN"
End If
End Select
End Function
 
Back
Top