Re: $$Excel-Macros$$ Formula or macro to find a word that is all capitlaized
Hi Try : Use below formula in Conditional Formatting =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT(65:90))),)))=3 Reference :http://dmcritchie.mvps.org/excel/strings.htm Thanks Mahesh On Thu, Jul 28, 2011 at 3:39 AM, qcan comeonove...@gmail.com wrote: Hi, Can anyone help me with some sort of formula or Macro that would identify any word (minmum 3 letters) that is all in caps. I would like whatever is found to be highlighted in yellow. Thanks. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Formula or macro to find a word that is all capitlaized
Exzcellent guys. Amazing. Thanks. On Jul 28, 8:18 am, XLS S xlst...@gmail.com wrote: Hey Ranjan qcan want if cell contain any 3 Capital letter then color hole cell.but your solution color only first 3 letter ... On Thu, Jul 28, 2011 at 5:32 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Hope it will Help you Sub HighLight() Application.ScreenUpdating = False On Error Resume Next Dim cell As Range Dim rng As Range Dim st As String Dim loc As Integer Dim n as string st = Set rng = ActiveSheet.UsedRange For Each cell In rng If cell.Value Then st = st cell.Value End If Next For i = 1 To Len(st) n = Mid(st, i, 1) Mid(st, i + 1, 1) Mid(st, i + 2, 1) If Asc(Mid(n, 1, 1)) 64 And Asc(Mid(n, 1, 1)) 91 And Asc(Mid(n, 2, 1)) 64 And Asc(Mid(n, 2, 1)) 91 And Asc(Mid(n, 3, 1)) 64 And Asc(Mid(n, 3, 1)) 91 Then Cells.Find(What:=n, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate loc = WorksheetFunction.Find(n, ActiveCell.Value, 1) ActiveCell.Characters(loc, 3).Font.ColorIndex = 26 ActiveCell.Characters(loc, 3).Font.Bold = True i = i + 3 End If Next Application.ScreenUpdating = True End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of qcan Sent: Thursday, July 28, 2011 3:40 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Formula or macro to find a word that is all capitlaized Hi, Can anyone help me with some sort of formula or Macro that would identify any word (minmum 3 letters) that is all in caps. I would like whatever is found to be highlighted in yellow. Thanks. -- ---- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- .- Hide quoted text - - Show quoted text - -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Formula or macro to find a word that is all capitlaized
Hope it will Help you Sub HighLight() Application.ScreenUpdating = False On Error Resume Next Dim cell As Range Dim rng As Range Dim st As String Dim loc As Integer Dim n as string st = Set rng = ActiveSheet.UsedRange For Each cell In rng If cell.Value Then st = st cell.Value End If Next For i = 1 To Len(st) n = Mid(st, i, 1) Mid(st, i + 1, 1) Mid(st, i + 2, 1) If Asc(Mid(n, 1, 1)) 64 And Asc(Mid(n, 1, 1)) 91 And Asc(Mid(n, 2, 1)) 64 And Asc(Mid(n, 2, 1)) 91 And Asc(Mid(n, 3, 1)) 64 And Asc(Mid(n, 3, 1)) 91 Then Cells.Find(What:=n, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate loc = WorksheetFunction.Find(n, ActiveCell.Value, 1) ActiveCell.Characters(loc, 3).Font.ColorIndex = 26 ActiveCell.Characters(loc, 3).Font.Bold = True i = i + 3 End If Next Application.ScreenUpdating = True End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of qcan Sent: Thursday, July 28, 2011 3:40 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Formula or macro to find a word that is all capitlaized Hi, Can anyone help me with some sort of formula or Macro that would identify any word (minmum 3 letters) that is all in caps. I would like whatever is found to be highlighted in yellow. Thanks. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel GiveCOlor.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Formula or macro to find a word that is all capitlaized
Hey Ranjan qcan want if cell contain any 3 Capital letter then color hole cell.but your solution color only first 3 letter ... On Thu, Jul 28, 2011 at 5:32 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Hope it will Help you Sub HighLight() Application.ScreenUpdating = False On Error Resume Next Dim cell As Range Dim rng As Range Dim st As String Dim loc As Integer Dim n as string st = Set rng = ActiveSheet.UsedRange For Each cell In rng If cell.Value Then st = st cell.Value End If Next For i = 1 To Len(st) n = Mid(st, i, 1) Mid(st, i + 1, 1) Mid(st, i + 2, 1) If Asc(Mid(n, 1, 1)) 64 And Asc(Mid(n, 1, 1)) 91 And Asc(Mid(n, 2, 1)) 64 And Asc(Mid(n, 2, 1)) 91 And Asc(Mid(n, 3, 1)) 64 And Asc(Mid(n, 3, 1)) 91 Then Cells.Find(What:=n, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate loc = WorksheetFunction.Find(n, ActiveCell.Value, 1) ActiveCell.Characters(loc, 3).Font.ColorIndex = 26 ActiveCell.Characters(loc, 3).Font.Bold = True i = i + 3 End If Next Application.ScreenUpdating = True End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of qcan Sent: Thursday, July 28, 2011 3:40 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Formula or macro to find a word that is all capitlaized Hi, Can anyone help me with some sort of formula or Macro that would identify any word (minmum 3 letters) that is all in caps. I would like whatever is found to be highlighted in yellow. Thanks. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- . -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Formula or macro to find a word that is all capitlaized
Hi, Can anyone help me with some sort of formula or Macro that would identify any word (minmum 3 letters) that is all in caps. I would like whatever is found to be highlighted in yellow. Thanks. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
