Conditional formating with 3 different rules applied

Hi,

I am trying to apply the below rules on number of failed backup. I can do the formula for Priority 3 but Priority 1 and Priority 2 overwrites Priority 3.

Rules:
Priority 1 if any cell value >= 3 continuous days (highlight Red)
Priority 2 If any value is one after another >= 3 times (ex 1, blank, 1, blank 1) (highlight Yellow)
Priority 3 If value >= 2 in the cell for 1 day only (highlight Blue)

Is it possible to use index and match or other formula linked to "DataFailBakup" or use macros? I am not very familiar in using macros.

Attached is the sample file for your reference.

Would really appreciate if can assist on this. Thank you.

Hi,
Can anyone assist? if this issues can be solved using macros, I don't mind trying out.

The team would like to have conditional formating as it's taking longer time to discuss and identifying using the pivot table way.

Would appreciate your great help on this.

Pl see file With Macro "MacroForColor"
Dashboard(2) is After running Macro

Please Login or Register  to view this content.

Hi,

Thank you for the macros. It is working but found some errors as attached.

1) It did not initialize when I select the next month for yellow and blue conditions
2) there should be continous detection when I select the next month

Please refer to attachment for viewing. Thank you.

Macro will not run automatically. You have to run manually whenever change done. otherwise worksheet events is to be used.

When I change the Month either to November or December at AK4 and ran the macro manually, it did not initialize the blank fields.

I have corrected code. Pl check now.

Wrong posting deleted.

Hi,

The management prefers to update automatically than key in the range manually after running macros.
Purpose to have quick discussion on backup failures during the meeting.

As per user's request, would like to have your advise using formulas than macros for Criteria 2 and 3 in conditional formulating.

Is the range for colouring L12:AP54 is fixed or it varies.

Yes, The file keeps increasing everyday in the Data worksheet and a formula has been set to identify new policies in the dashboard of Column K.

Pl see file
Worksheet event is used for Dashboard. When month is changed worksheet_change event runs macro automatically.

Please Login or Register  to view this content.

Hi,

Thanks a lot for your effort. It worked beautifully. It has saved a lot of my time identify.
I am trying to understand the macros but I need a lot of time to understand it.

Hi,

I have an issue where the macro is not highlighting for Red and yellow color when the same policy overflows to next month. For example

31/10/15, 1/11/15 and 2/11/15 should hightlight as red but it's not highlighting
29/10/15, 31/10/15 and 2/11/15 should highlight as yellow but it's not highlighting

Should I continue the same thread eventhough this thread is solved coz I am still having issues. Or should I create a new thread.
Please advise.

Include all dates in next columns. as per your file AP is the last column. In the macro same is included. After including the dates as said Find the last column and replace AP by that column, then run the macro.Change is to be done in
Set IPrng = Range("L12:AP" & LR)

The cell where month was in AK4 now changed to E7. Accordingly worksheet_event is changed. Now it is working.

You are absolutely brilliant. It worked beautifully. It didn't came accross this method in my mind.
I have learnt a lot from both of you and I loved it. My bosses loved this dashboard.

Thank you for the effort and really appreciate it.

Welcome suchetherrah.

Hi ChemistB,
I did not refer to your file. I referred to File 3-different-rules-applied-failbackupmacro ans.xlsm only.
Regards
KVS‎