2 in 1: Conditional formatting and Data reorganization.

[Neck_Deep]

From reading the form I have to admit I'm a little shy to ask these two questions and not feel a little... well, Dumb.

First Question: I have a set of data with column headers from A through XX. The data in each row has information about specific events over a large time frame. This data can be up to 4000 to 5000 rows of data. Specifically, I want to format the entire row if a value of greater than zero appears in the row within column T. I typically would go to the conditional formatting tab and create rule like =indirect("T"&ROW()) then establish the font color and font style. Once I apply to the entire sheet, everything looks great.

I'm using Excel 2007 and when I try to record this with the Macro record function I fail. It just doesn't work. Any thoughts?

Second question: while this set of data isn't nearly as long, I still struggle to visualize what the code would look like. Headers across the top contain column headers like client ID, name, address, and so on. I want to organize this info so that if multiple client names appear under the same client ID number it will pull the client data into a organized format down the left with the client ID in column A and column B has the names associated with the address, phone and so on underneath. Almost like a pivot table, but without the horizontal length you tend to get when you start adding lots of column values. My client data can have as much as 32 to 40 columns of information. This makes it hard when reading the data because you have to scroll to the right. Thoughts?

ti know I'm asking a lot and not providing screenshots and whatnot, but any thoughts would help. Which function would be best. Thank you.

 

[Akashwani]

Hi and welcome to MrExcel.

**I do not write or understand VBA, so I cannot help you further with coding, sorry.**

First Question.

I have recorded the following macro (which can be trimmed down, by an expert) to highlight the complete row based on the value in column T being greater than 0.

Sub TestCF()
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$T2>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A2").Select
End Sub

Results when macro has run....

Excel Workbook
	A	B	C	S	T	U	V	W
1	Data1	Data2	Data3	Data19	Data20	Data21	Data22
2	Bill	Tim	1	Cat	1	Bike	Sun
3	Bob	Sam	1	Dog	0	Bus	Moon
4	Ben	Sue	1	Pig	2	Tram	Star
5	Bud	Dan	2	Cow	0	Taxi	Rain
6	Ted	Den	1	Rat	0	Train	Snow
7	Tom	Don	1	Duck	3	Plane	Hail
8
Sheet3

Second question.

Other than applying Filters, Freeze Frame and Sorting your data based on Client ID, I have no idea how you can achieve what you require.

I'm sorry that I couldn't be of more/better help to you, but I'm sure someone will jump in with their take on your situation.

Ak

 

[Neck_Deep]

Hi and welcome to MrExcel.

**I do not write or understand VBA, so I cannot help you further with coding, sorry.**

First Question.

I have recorded the following macro (which can be trimmed down, by an expert) to highlight the complete row based on the value in column T being greater than 0.

Sub TestCF()
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$T2>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A2").Select
End Sub

Results when macro has run....

Sheet3

*	A	B	C	S	T	U	V	W
1	Data1	Data2	Data3	Data19	Data20	Data21	Data22	*
2	Bill	Tim	1	Cat	1	Bike	Sun	*
3	Bob	Sam	1	Dog	0	Bus	Moon	*
4	Ben	Sue	1	Pig	2	Tram	Star	*
5	Bud	Dan	2	Cow	0	Taxi	Rain	*
6	Ted	Den	1	Rat	0	Train	Snow	*
7	Tom	Don	1	Duck	3	Plane	Hail	*
8	*	*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:30px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Second question.

Other than applying Filters, Freeze Frame and Sorting your data based on Client ID, I have no idea how you can achieve what you require.

I'm sorry that I couldn't be of more/better help to you, but I'm sure someone will jump in with their take on your situation.

Ak

AK,

thank you. I'll insert this in my larger Code and based on what you wrote I think it should work. Thanks again for the help.

 

[Akashwani]

Hi,

You can delete the quoted text that automatically gets added when you post a reply!

If you need additional help with the VBA code, then I suggest that you start a new post with a title like "VBA Conditional Formatting code needs editing".

Good luck with your project and thanks for your feedback.

Ak