 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008Change Cell color with macro
hello all, I was wondering if anyone could help me out with this. I want to be able to change the background and font color of any cell where text is equal to a specific text. I do not know if I have the right syntax, but here goes If text = "Fail" then Range("A1:J19").Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.ColorIndex = 2 Selection.Font.Bold = True Else If text = "Pass" Then Range("A1:J19").Select Selection.Interior.ColorIndex = 5 Selection.Font.ColorIndex = 2 Selection.Font.Bold = False Else If Text = "WIP" Then Range("A1:J19").Select With Selection.Interior .ColorIndex = 10 .Pattern = xlSolid End With Selection.Font.ColorIndex = 2 Selection.Font.Bold = True End If End If End If Any and all help in the matte is greatly appreciated > hello all, > [quoted text clipped - 31 lines] > > Any and all help in the matte is greatly appreciated How is [text] referenced? Do you get it from a Cell, or is it in your VBA-Process? If you only need to distinguish between 3 statements (Fail, Pass and WIP) then i would recommend "Conditional Formatting" You can select Range A1:J19, then goto Format --> Conditional Formatting, there you choose "Formula is" and then you enter =$A$20="Fail" then you select the Pattern and everything, then you press Add... and do the same again for Pass and WIP. You only have 3 possibilities...if you need more then we have to go back to your VBA code. (Look up "Select Case" and don't use selection, but we can check that later) Hth Carlo No I also have the following text Pending Broken Running Not Completed Each Week I have to update this spreadsheet A B C D E 1 ENV 02/01/08 02/08/08 02/15/08 2 Web Pass Pass Broken 3 MFrame Pass Fail Fixed 4 GUI WIP Pending Running 5 after our health check we go into this spreadsheet and update it. I was using conditional formatting but I only could add three conditional. also since they shared the workbook to everyone, conditional formatting is no longer working.... That is why i was trying to create a macro that we could run that would change the cell background and font based on the text in the cell Thanks for the help Argus On Feb 8, 3:02 pm, "Argus Rogue" <argusro...@satx.rr.com> wrote: > hello all, > [quoted text clipped - 32 lines] > > Any and all help in the matte is greatly appreciated How is [text] referenced? Do you get it from a Cell, or is it in your VBA-Process? If you only need to distinguish between 3 statements (Fail, Pass and WIP) then i would recommend "Conditional Formatting" You can select Range A1:J19, then goto Format --> Conditional Formatting, there you choose "Formula is" and then you enter =$A$20="Fail" then you select the Pattern and everything, then you press Add... and do the same again for Pass and WIP. You only have 3 possibilities...if you need more then we have to go back to your VBA code. (Look up "Select Case" and don't use selection, but we can check that later) Hth Carlo try Sub colorcells() mc = "e" lr = Cells(Rows.Count, mc).End(xlUp).Row For Each c In Range(Cells(2, mc), Cells(lr, mc)) Select Case UCase(c) Case "PENDING": myc = 5 Case "BROKEN": myc = 36 Case "RUNNING": myc = 8 'Not Completed 'as many as you need Case Else End Select c.Interior.ColorIndex = myc Next End Sub  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > No I also have the following text > Pending [quoted text clipped - 77 lines] > Hth > Carlo > try > Sub colorcells() [quoted text clipped - 101 lines] > > - Show quoted text - it only colors or updates column "e". How can I get it to do the whold sheet > > try > > Sub colorcells() [quoted text clipped - 109 lines] > > - Show quoted text - You could add following part to the sub: Sub colorcells() mc = "e" lr = Cells(Rows.Count, mc).End(xlUp).Row For Each c In Range(Cells(2, mc), Cells(lr, mc)) Select Case UCase(c) Case "PENDING": myc = 5 Case "BROKEN": myc = 36 Case "RUNNING": myc = 8 'Not Completed 'as many as you need Case Else End Select c.entirerow.Interior.ColorIndex = myc Next End Sub hth Carlo Sub colorALLcells() 'lr = Cells(Rows.Count, "a").End(xlUp).Row 'For Each c In Range(Cells(2, "b"), Cells(lr, "d")) For Each c In ActiveSheet.UsedRange Select Case UCase(c) Case "PENDING": myc = 5 Case "BROKEN": myc = 36 Case "RUNNING": myc = 8 'Not Completed 'as many as you need Case Else myc = 0 End Select c.Interior.ColorIndex = myc Next End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com On Feb 8, 11:34 am, "Don Guillett" <dguille...@austin.rr.com> wrote: > try > Sub colorcells() [quoted text clipped - 106 lines] > > - Show quoted text - it only colors or updates column "e". How can I get it to do the whold sheet |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.