Microsoft Excel – Using VBA to check values in different columns and apply formatting

Microsoft Excel LogoExcel it a complicated beast.  When I first started with spreadsheets I was always putting things in columns and rows and not doing much with it.  Since learning VBA about 15 years ago I have taken my knowledge to the Nth degree.  Recently, I ran a VBA workshop and one of the delegates asked me a question which perplexed me for a while.

“I want to be able to check two values and if they are equal then I want the “check” column to turn green and a plus sign be the value.  Oppositely if the cell values don’t match then I want the “checked” cell value to be a minus sign and the cell colour to be red.”

Seems simple doesn’t it.  Now try and do it without conditional formatting because that was my first thought too.

The first thing I did was create a column (just around 10 rows) with single figure values.

[picture]

I decided that column C was going to be the check column.

So straight into the VB Editor (ALT+F11).  I insert a new module on the corresponding workbook. and start typing a very simple function.  Remembering that I have to pass two variables (column a value and column b value).

Function verify(xval,yval)

if xval = yval then

verify = “+”

else

verify = “-“

end if

End Function

Now that couldn’t be simpler.

However, in functions its not possible to do any cell formatting.  I’ve googled it and everything formatting wise is done via macros.  So the next part is changing the cell colours to either green or red.  Now this is done via an WORKSHEET_CHANGE sub which doesn’t sit in the modules (they are reserved for macros and functions).  It actually sits on the WORKSHEET element within the VB Editor.

VBA_Editor_Worksheet

 

Private Sub Worksheet_Change(ByVal Target As Range)
For Each Cell In Range(“C1:C100”)

Select Case Cell.Value

Case “+”

Cell.Interior.ColorIndex = 4

Case “-“

Cell.Interior.ColorIndex = 3

Case Else

Cell.Interior.ColorIndex = 0

End Select

Next Cell

End Sub

Now you will see at the top of the function we’ve defined a range – D1:D100.  Now anything outside these cells WILL NOT trigger this function.  If you need to change the column reference then please feel free to do so.  Just remember that the longer your worksheet becomes you will have come back to this function and extend / change the range.

 

Would love to hear your comments of success on implementing this.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *