Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally format row height
From looking around it appears as though row height cannot be conditionally
formatted without some VB script. Here is my dilemma and I was hoping that someone could give me an example of some code if it isn't too complicated or time consuming. I am importing data from a SQL query with 7 columns and a variable number of rows. One of the columns is "Type". There are 3 possible types: Requirement, Enhancement and Defect. With the first two types, I would like the row height to be large so that I can see all the information in the cells for a given row (i.e., I don't want anything truncated). However to cut down on the size of the spreadsheet, I would like to adjust every row that is under type "defect" to be smaller (say row height = 30). Thanks for any assistance Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally format row height
Hi,
Something like this ..... Sub SetRowHeight() ' Assume data is in column B (change to your requirement) Set rng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row) For Each Cell In rng If Cell.Value = "Defect" Then Cell.RowHeight = 20 Else Cell.RowHeight = 40 End If Next End Sub HTH "CDAK" wrote: From looking around it appears as though row height cannot be conditionally formatted without some VB script. Here is my dilemma and I was hoping that someone could give me an example of some code if it isn't too complicated or time consuming. I am importing data from a SQL query with 7 columns and a variable number of rows. One of the columns is "Type". There are 3 possible types: Requirement, Enhancement and Defect. With the first two types, I would like the row height to be large so that I can see all the information in the cells for a given row (i.e., I don't want anything truncated). However to cut down on the size of the spreadsheet, I would like to adjust every row that is under type "defect" to be smaller (say row height = 30). Thanks for any assistance Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally format row height
This should be close... It assumes type is in Column C and that you are using
"Sheet 1" for your data. You can change both of those at the set statements... Public Const m_cDefect As String = "Defect" Public Const m_cRowHeightSmall As Integer = 30 Public Const m_cRowHeightlarge As Integer = 60 Public Sub AdjustRowHeight() Dim rngCurrent As Range Dim wksCurrent As Worksheet Set wksCurrent = Sheets("Sheet1") Set rngCurrent = wksCurrent.Range("C65535").End(xlUp) Do While rngCurrent.Row 1 If rngCurrent.Value = m_cDefect Then rngCurrent.RowHeight = m_cRowHeightSmall Else rngCurrent.RowHeight = m_cRowHeightlarge End If Set rngCurrent = rngCurrent.Offset(-1, 0) Loop End Sub -- HTH... Jim Thomlinson "CDAK" wrote: From looking around it appears as though row height cannot be conditionally formatted without some VB script. Here is my dilemma and I was hoping that someone could give me an example of some code if it isn't too complicated or time consuming. I am importing data from a SQL query with 7 columns and a variable number of rows. One of the columns is "Type". There are 3 possible types: Requirement, Enhancement and Defect. With the first two types, I would like the row height to be large so that I can see all the information in the cells for a given row (i.e., I don't want anything truncated). However to cut down on the size of the spreadsheet, I would like to adjust every row that is under type "defect" to be smaller (say row height = 30). Thanks for any assistance Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally format row height
That worked really well but I wasn't clear enough. For the rows that aren't
of type defect, I would like to have a variable row height that adjusts to fit the data in the cell. Does this make it a lot more complicated? Thanks again for the help "Toppers" wrote: Hi, Something like this ..... Sub SetRowHeight() ' Assume data is in column B (change to your requirement) Set rng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row) For Each Cell In rng If Cell.Value = "Defect" Then Cell.RowHeight = 20 Else Cell.RowHeight = 40 End If Next End Sub HTH "CDAK" wrote: From looking around it appears as though row height cannot be conditionally formatted without some VB script. Here is my dilemma and I was hoping that someone could give me an example of some code if it isn't too complicated or time consuming. I am importing data from a SQL query with 7 columns and a variable number of rows. One of the columns is "Type". There are 3 possible types: Requirement, Enhancement and Defect. With the first two types, I would like the row height to be large so that I can see all the information in the cells for a given row (i.e., I don't want anything truncated). However to cut down on the size of the spreadsheet, I would like to adjust every row that is under type "defect" to be smaller (say row height = 30). Thanks for any assistance Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditionally format anumber format | Excel Worksheet Functions | |||
How do I conditionally format a chart? | Charts and Charting in Excel | |||
conditionally format cells | Excel Worksheet Functions | |||
How do I conditionally format a chart? | Excel Discussion (Misc queries) | |||
Conditionally format | Excel Programming |