Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

Excel Macro-

I want to write a macro which will check whether in a column every row contents end in "END". If so colur that cell with red.

Eg If a cell contains - "YEAR-END" then it will be colured red. If a cell contains "END-YEAR" it will be left like that this has to be chhecked for all cels in Column B
0
michaelm702
Asked:
michaelm702
  • 2
1 Solution
 
RogueSolutionsCommented:
You don't need a macro.

Assuming that column B has your values that need testing ...

Select all of Column B

Add a conditional format to whatever cell you want to have change colour.

Goto Format ... Conditional Formatting

Change Cell Value Is to Formula Is

Type the following ...

=UPPER(RIGHT(B1,3))="END"

Then click Format, select Patterns and choose a red fill.

Click OK

0
 
jimbobmcgeeCommented:
If you do particularly want a macro, you can use:

    Sub RedIfEnd()
   
        Dim rng As Range        

        For Each rng In Selection.Cells
            If Right(Trim(UCase(rng.Value)), 3) = "END" Then rng.Interior.Color = RGB(255, 0, 0)
        Next rng
       
    End Sub

This will also omit any trailing spaces from the cell value (so it will turn red for 'this-end' and 'this-end   ' but not 'end-this' -- no quote marks, of course)

HTH

J.
0
 
jimbobmcgeeCommented:
As far as I can see, either solution works fine...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now