Thank you Don for the help. Your input was very valuable and it fixed the
color shading on column "H"
I need to bother you again and send me a quick Sub to do the following:
Whenever there is a value on a cell located on column "H" (it could be a
value or a formula). I need a routine that can drawn a single line (color
black) from column A to column H
But just on the line where cell "H" contains a value. If cell "H" is
blank. I need the routine to loop until it finds the last cell "H" that
contains a value
As always, thank you for your input and help
On Tue, Jul 31, 2012 at 10:58 AM, dguillett1 <dguille...@gmail.com> wrote:
> I would completely re-write your macro to simplify and remove
> selections but this solves your immediate problem
>
> Sub colorcolH()
> Columns("H").SpecialCells(xlFormulas, xlNumbers).Interior.ColorIndex = 6
> End Sub
>
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>
> *From:* CAN <cdelano...@gmail.com>
> *Sent:* Tuesday, July 31, 2012 9:33 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Macro is not doing the right formatting -
> need help
>
> Thank you Don, you are correct. I should provide a more clear
> explanation of what I need.
>
> I have attached 2 additional files that shows what happens when I run the
> original macro shown on this emai (file_after_Original_macro.xksx)l. You
> will see that not all the cells are formatted the same. The second file
> (file_the_way_it_Should_be.xlsx) shows the way I need the macro to works,
> with the cells formatted correctly.
>
> I hope this helps.
>
> Once again, Thank you a whole bunch!!
>
> On Tuesday, July 31, 2012 9:52:01 AM UTC-4, Don Guillett wrote:
>
>> You should, at least provide an .xlsM file with your macro and
>> before/after examples of what you want. Why should we do extra work?
>> I will then be happy to have a look.
>>
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>
>> *From:* CAN <cdelano...@gmail.com>
>> *Sent:* Tuesday, July 31, 2012 8:17 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* $$Excel-Macros$$ Macro is not doing the right formatting -
>> need help
>>
>> I have a simple excel sheet (attached file) The file is sorted by
>> date and it shows information about deposits made in a day. Sometimes a
>> deposit might have more than 1 transaction, sometimes it is just one
>> transaction.
>> The data is as follows: Date, Unit ID, Amount & Check number (there
>> other columns (B, D & F) which are hidden for formatting purposes.
>>
>> I have a macro ( see if below ) which I run to group and format the
>> data. It simple does the following:
>>
>> 1) adds color to the heading, calculates a subtotal based on date, draws
>> a line at the end of each date and colors the cell where the subtotal
>> calculation is shown.
>>
>> For most part the macro works well, except that when a day deposit has
>> just one item, it doesn't format the cells as when the deposit has more
>> than one item.
>>
>> If you run the macro, you'll see that when the deposit has just one
>> item, the line to create an indication of "grouping items by day" does
>> not work, and also the subtotal cell is not highlighted in yellow like the
>> others.
>>
>> I would appreciate you suggestions and comments to see if I can have this
>> macro works as it is intended
>>
>> Thank you a whole bunch
>>
>> Here is the macro:
>>
>> Sub Subs_T_Complete()
>> '
>> ' Subs_T_Complete Macro
>> '
>> '
>> Sheets("Fixed").Select
>> Range("F1:F600").Select
>> Selection.Copy
>> Sheets("New").Select
>> Range("F1").Select
>> Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
>> SkipBlanks:=False, Transpose:=False
>> Sheets("Fixed").Select
>> Range("H1:H600").Select
>> Application.CutCopyMode = False
>> Selection.Copy
>> Sheets("New").Select
>> Range("H1").Select
>> Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
>> SkipBlanks:=False, Transpose:=False
>> Range("I9").Select
>> Application.CutCopyMode = False
>> Application.Run "PERSONAL.XLS!All_together"
>> Range("A2:G600").Select
>> Selection.Interior.ColorIndex = xlNone
>> Columns("H:H").Select
>> Selection.Style = "Comma"
>> Range("B1").Select
>> ActiveCell.FormulaR1C1 = "1"
>> Range("D1").Select
>> ActiveCell.FormulaR1C1 = "2"
>> Range("B1").Select
>> Selection.Font.ColorIndex = 5
>> Range("D1").Select
>> Selection.Font.ColorIndex = 14
>> Selection.Font.ColorIndex = 5
>> Range("K2").Select
>> ActiveSheet.Shapes.AddTextbox(**msoTextOrientationHorizontal, 403.5,
>> 13.5, _
>> 240.75, 33#).Select
>> Selection.Characters.Text = "Subtotals listed by date"
>> With Selection.Characters(Start:=1, Length:=24).Font
>> .Name = "Calibri"
>> .FontStyle = "Regular"
>> .Size = 11
>> .Strikethrough = False
>> .Superscript = False
>> .Subscript = False
>> .OutlineFont = False
>> .Shadow = False
>> .Underline = xlUnderlineStyleNone
>> .ColorIndex = 1
>> End With
>> Range("N5").Select
>> End Sub
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>> ------------------------------**------------------------------**
>> ------------------------------**------------
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> To unsubscribe, send a blank email to excel-macros+unsubscribe@**
>> googlegroups.com <excel-macros%2bunsubscr...@googlegroups.com>
>>
>>
>>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
> To unsubscribe, send a blank email to
> excel-macros+unsubscr...@googlegroups.com
>
>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
> To unsubscribe, send a blank email to
> excel-macros+unsubscr...@googlegroups.com
>
>
>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
