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

  • 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
  • 2
1 Solution
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 ...


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

Click OK

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)


As far as I can see, either solution works fine...

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