Simple excel question..

[rOAdeh]

Hi guys,

I'm trying to make a simple spreadsheet for the company I work for. Basically they need to enter some data and if a value is outside of a certain range in a column it needs to be flagged up. Ideally this would be done by highlighting the text as a different colour.

I imagine I need to use macros for this but have no real experience of using them. The websites i've looked at and and the in-built help have both been useless too for what I'd expect to be fairly simple.

I'd guess the macro would be something like:

Sub Macro1()
'
' Macro1 Macro

#If (A1 > 3) Then
'. Selection.Font.ColorIndex = 3
#ElseIf (A1 < 0) Then
'. Selection.Font.ColorIndex = 2
#Else
'. Selection.Font.ColorIndex = 0
#End If

End Sub

but I don't know how I can apply that macro to work with a specific column, nor if indeed it'll do what I want it to.

Thanks for any help,

Ben

 

[denz1968]

Hi

Use Format, conditional formatting

Enter your max value as criteria then select the format button and tell it to change colour of your choice if the criteria is met.

Do the same for your minimum value and choose a different colour

 

[madmaxx]

Do what denz said, its by far the easiest way to change formats depending on the contents of a cell. Once youve set the format for the maximum value, you then need to click the 'Add>>' button to add another criteria - set this one to format numbers below the minimum value.
Hope that helps.

 

[rOAdeh]

thanks guys, had completely forgotten about this thread as i stumbled upon the answer myself eventually! ..much easier than having to mess about with macros