Ryan has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Change cell colour of row based upon text contained in a cell
Good afternoon,
Please can you help me?
I am trying to write a piece of code that will change the colour of the cells in a given row, based upon part of the text contained within a specific cell of the given row.
For example, the ninth column of my data may contain the words "move to" with a date, I would like to colour any rows where this text is contained within a cell to a particular colour.
I know how to do this if the string being searched is the entire cell content, but not only if it is contained within part of the cell.
Sorry for the waffle, I hope I have explained myself sufficiently.
Kind thanks,
Ryan
RE: Change cell colour of row based upon text contained in a cel
Hi Ryan, you don't need to do this with code you can do it simply with conditional formatting.
Select all your data excluding the headings
Click Conditional Formatting on the Home tab
Choose New Rule from the drop down
Choose "Use a formula to determine which cells to format"
In the field below enter:
=IF(ISNUMBER(SEARCH("*move to*",$I1)),TRUE, FALSE)
...altering $I1 to be the first cell in the field you are testing, keeping the absolute reference on the column
Click format and apply any formatting you want
Click OK and check if your conditional formatting has been applied
If it hasn't, it's probably due to a Microsoft bug. Go back to the Conditional formatting drop down and choose Manage Rules. Click on your rule and then on Edit Rule. See if the cell reference has been altered to something like $I1048576. change this back to $I1 and reapply. That should do the trick.
Hope this helps,
Anthony
RE: Change cell colour of row based upon text contained in a cel
Hi Anthony,
Thank you for your response, the reason that I wanted to do this via VBA is that the purpose of this was to automate the formatting of the data and as such would be part of a larger code.
Is there a way that this can be included in VBA?
Kind thanks,
Ryan
RE: Change cell colour of row based upon text contained in a cel
Hi Ryan. Code-wise there are many different ways of doing this. Probably the simplest is to leverage the Autofilter function:
*******************************
Sub FormatMe()
Selection.AutoFilter
ActiveSheet.Range("$A$1:$L$17").AutoFilter Field:=9, Criteria1:= _
"=*move to*", Operator:=xlAnd
ActiveCell.CurrentRegion.Select
Selection.Interior.Color = vbRed
ActiveSheet.ShowAllData
Selection.AutoFilter
Range("A1").Select
End Sub
*******************************
Hope this helps,
Anthony
