Hiliting cells a different color

[gaz8000]

Is there a way of hiliting cells a different color depending on value?

For instance for all columns that contain the text 'complete' and the underlying rows are N or P then color blue.

Can this be done

TIA

 

[sykes]

Research "Conditional formatting" for this.
There will be hundreds of posts here, but you'll probably be able to work it out for yourself if you look at in within Excel.

 

[prajul89]

See conditional Formatting in your Help.
That might help

 

[gaz8000]

I know it can be done manually, i was thinking via VBA as its massive and takes hours manually

 

[sykes]

.............so you need to say that in your original post!

 

[laughingdevil]

Do you have the condition you would use to do it manually in one cell? Converting that to vba should be fairly straightforward.

 

[laughingdevil]

As an example the below will colour the selected cell red if it contains the word "Bob"

Were you to stick a select all for the sheet in question before it, and adapt the formula and colours it should work

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""Bob"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

There is probably a more efficent way to do it than the above (grabbed by the macro recorder) using the .formatconditions method if you are willing to do a bit of digging.