Totally buy that Don. Cheers, Sam
On Wed, Nov 16, 2011 at 12:50 AM, dguillett1 <dguille...@gmail.com> wrote: > Sam, I am one of those that does NOT like a lot of CF or even a lot of > data validation. So, why not take a lot of the work away from the user and > not clutter up the file with cf and dv. Let me know what you think of this > idea to just double click col A>select number to fill in Text and color row. > > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As > Boolean) > If Target.Count > 1 Or Target.Column <> 1 Then Exit Sub > Dim mn As Double > Dim x As String > Dim y As Long > > Dim msg As String > msg = "Enter 1 for One" > msg = msg & vbLf & "Enter 2 for Two" > msg = msg & vbLf & "Enter 3 for Three" > msg = msg & vbLf & "Enter 4 for Four" > msg = msg & vbLf & "Enter 5 for Five" > > mn = Application.InputBox(Prompt:=msg, Title:="Enter a Number", > Type:=1) > If mn < 1 Or mn > 5 Then > MsgBox "Invalid entry", vbExclamation > Exit Sub > End If > > Select Case mn > Case 1: x = "One": y = 12 > Case 2: x = "Two": y = 22 > Case 3: x = "Three": y = 6 > Case 4: x = "Four": y = 5 > Case 5: x = "Five": y = 4 > Case Else > End Select > > Target = x > Rows(Target.Row).Interior.ColorIndex = y > End Sub > > Don Guillett > SalesAid Software > dguille...@gmail.com > > *From:* Sam Mathai Chacko <samde...@gmail.com> > *Sent:* Tuesday, November 15, 2011 11:38 AM > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ Conditional formatting of entire row > using VBA > > FJO, if you are using Excel 2003 or less, then I am afraid the solution I > posted will not be very useful, as it requires more than 3 conditionals, > which only Excel 2007+ supports. > > So in that case, Don's VBA code should suit you fine. But if you are in > Excel 2007 or 2010, you could use multiple conditionals for formatting > (which is what I'd prefer, it also helps in reducing VBA) > > Regards, > Sam > > On Tue, Nov 15, 2011 at 10:35 PM, dguillett1 <dguille...@gmail.com> wrote: > >> Sam, I’m sorry.I opened in xl2003........................duh >> >> Don Guillett >> SalesAid Software >> dguille...@gmail.com >> >> *From:* Sam Mathai Chacko <samde...@gmail.com> >> *Sent:* Tuesday, November 15, 2011 10:51 AM >> *To:* excel-macros@googlegroups.com >> *Subject:* Re: $$Excel-Macros$$ Conditional formatting of entire row >> using VBA >> >> Yes Don. I was just showing how it is done for one conditional. I had >> it done for One to Five in the attachment I had posted. >> >> Regards, >> >> Sam >> >> On Tue, Nov 15, 2011 at 10:07 PM, dguillett1 <dguille...@gmail.com>wrote: >> >>> Sam, >>> > "one" >>> > "two" >>> >>> Don Guillett >>> SalesAid Software >>> dguille...@gmail.com >>> >>> *From:* Sam Mathai Chacko <samde...@gmail.com> >>> *Sent:* Tuesday, November 15, 2011 10:21 AM >>> *To:* excel-macros@googlegroups.com >>> *Subject:* Re: $$Excel-Macros$$ Conditional formatting of entire row >>> using VBA >>> >>> Here's how you do it. Select your entire row. Assuming your top left >>> cell in the entire selection is A1 (will be any one cell in the first >>> column depending on where you started your selection) >>> >>> Go to conditional format, and in the options 'Use formula to determine >>> which cells to format', write =$A1="One" >>> >>> and then apply the required format you need. >>> >>> Check the attachment. >>> >>> Regards, >>> >>> Sam Mathai Chacko >>> >>> On Tue, Nov 15, 2011 at 7:19 PM, FJO <goo...@finndj.dk> wrote: >>> >>>> Hi >>>> >>>> Can anyone help me with the VBA code for conditional formatting in >>>> Excel. >>>> >>>> I have af worksheet of 1000 rows with 5 colums. I have data in colum >>>> B;E and would like Excel to format the rows in a colour depending of >>>> what I write in colum A. >>>> >>>> I have 5 different text imput I can chose for each row, lets call >>>> them >>>> "one" >>>> "two" >>>> "Three" >>>> "Four" >>>> "Five" >>>> >>>> ...just to keep this example as simple as possible. >>>> >>>> If I write "one" in A2, I would like Excel to color Row 2 in one >>>> color, if I write "Two" then another color and so on. >>>> >>>> I found out how to color the cell I changed, but I would like to have >>>> the whole row colored. >>>> >>>> I hope someone can send me an example. >>>> >>>> -- >>>> FORUM RULES (934+ 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 >>>> >>> >>> >>> >>> -- >>> Sam Mathai Chacko >>> -- >>> FORUM RULES (934+ 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 >>> -- >>> FORUM RULES (934+ 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 >>> >> >> >> >> -- >> Sam Mathai Chacko >> -- >> FORUM RULES (934+ 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 >> -- >> FORUM RULES (934+ 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 >> > > > > -- > Sam Mathai Chacko > -- > FORUM RULES (934+ 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 > > -- > FORUM RULES (934+ 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 > -- Sam Mathai Chacko -- FORUM RULES (934+ 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
