script for conditional formatting

[Crispy85]

Hi,

I tried recording my conditional formatting settings but for some reason it doesn´t write it down in the script.

I would like the format to be:

When a value in column O is bigger than 0, color the entire row red and text bold, for which has this value in range (A2:Lastcell).

Does someone has a script like this?

Thanks,
Crispy

[NeedForExcel]

See the attached file..

Try this - Select Data Excluding Headers > Alt+O > D > New Rule > Use Formula To Determine > =$O2>0 > Apply Formatting

[Rob K]

Hi Crispy,

I was looking at doing conditional formatting in VBA, but the upshot was that it is much slower than the Excel conditional formatting, so if it is possible to set the CF on the sheet then that is definitely best.

The only reason I could find for scripting CF is if you are adding a worksheet within your macro and you want it to automatically populate the CF. The only other reason is if you want to use more than 3 conditional formats in a cell, then you have to use VBA. Avoid if, like me, you are new to VBA, you will be adding research time to your efficiency.

NeedForExcel's answer should solve your problem.

Kind regards
Rob

[Crispy85]

Hi both,

Yes NeedForExcel, that's how i created the conditional formatting too. However, this requires a manual step that i'm trying to avoid.
The Macro creates a new sheet every time where it needs to put this formatting. So it isn't a fixed template where i can pre-set conditional formatting.

Is there then maybe a code that does color the row right and text bold whenever a value in O:O is >0?

something like:

Please Login or Register  to view this content.

[Crispy85]

Oh wait. My script actually worked... what the.

Thanks guys!!!

[Crispy85]

But i want it to color the whole row.

i tried something like this but it doesn't work

[CODE][/Sub color()

Dim myRange As Range
Dim cell As Range
Set myRange = Range("O2:O" & Rows.Count)
For Each cell In myRange
If cell.Value > 0 Then cell.EntireRow.cell.Font.ColorIndex = 5
NextCODE]

any ideas?

[Crispy85]

*Accidental double post because of server problems*

[Crispy85]

Hi.
Found the problem. Might as well be usefull for some other people in the future:

Please Login or Register  to view this content.

This macro gives the color blue (5) to all the rows, where in column O the value is bigger than 0.