Value & ".xls" 'You might want to change the extension (.xls) according to your excel version Active Workbook. Cut Copy Mode = False End Sub Thanks Trowa, The code is working perfect, can you also help me if I want the output without 1st row means without name as the workbook is named with the name. The following code doesn't copy the first row and has 4 extra lines to change Row Height, Column Width, Font and Font Size. You could repeat the lines for other columns or apply the changes to multiple columns as in Columns("A: C") [or Rows("1:2") for Row Height]. Here is the code: Sub Run Me()Dim l Row, l Col As Integer Sheets("Master"). Screen Updating = False Red Color = 3Green Color = 43Blue Color = 23Purple Color = 47Orange Color = 44my Col1 = Input Box("Enter column letter of the first column to count colors:", "Input column letter")my Col2 = Input Box("Enter column letter of the second column to count colors:", "Input column letter")my Col3 = Input Box("Enter column letter of the third column to count colors:", "Input column letter")my Col4 = Input Box("Enter column letter of the fourth column to count colors:", "Input column letter")x = 1Do Workbooks("Source.xls").
Here is the code: Sub Count Colors()Dim Red Color, Green Color, Blue Color, Purple Color, Orange Color, x, y As Integer Dim Red Count, Green Count, Blue Count, Purple Count, Orange Count As Long Dim my Col, my Col1, my Col2, my Col3, my Col4 As String Dim ws As Worksheet Application.
now I want a report in a new workbook which will give me a count as Name as header and color in one cell and its value in other cell for all the colors that I have mentioned above.
and in the other column i:e Address I may highlight some other rows with all other colours.
Screen Updating = False Dim My Color As Long Dim My Count As Long My Color = 47 Dim cell As Range For Each cell In Selection If cell. Color Index = My Color Then My Count = My Count 1 Next cell Msg Box "There are " & My Count & " cells that are this color" Application. Open "C:\My Documents\Destination.xls"Go To Cont End Sub Hi Trowa, Thank you for the code.. Screen Updating = True Exit Sub Open Book:'Change path to match yours Workbooks. Do you want to overwrite old color report when new report is created? Could be fixed numbers of columns as well), to create all color reports at once? so possibly I will be highlighting some names with red, some with green and so on. Paste all 5 sentences in a cell or spread across multiple rows? Do you want to loop through all columns with data in the 1st row (or whatever row, to know which columns are being used. Best regards, Trowa Hi Trowa, I have a sheet were 3 to 4 columns are filled with n number of rows and every column has a header like Name, Address etc.