Conditional Formatting help

Dazdup

New Member
Joined
Aug 25, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi everyone,

I have been trying to create a spreadsheet for work with various Conditional Formatting rules, however I have got a bit stuck. I am new to this excel stuff, and I am trying to do the following;

- When the O column shows "Complete", I want to have the whole row in question show green (to show that it has been done)
- When the O column shows "Enquiry sent", I want to have the whole row in question show yellow (to show that an order has been placed)
- When the O column shows "Incomplete", I want it to follow a series of commands dependant on time left in the N column. (so we know when to place the order)
- If the O column is "Incomplete", I want the colour that is shown in the N Column to be copied over to the rest of the row.
- The N row shows different colours based on the amount of days left to the deadline date.


Included is an image of what I have already attempted.

Thanks alot, Daz
 

Attachments

  • 1.jpg
    1.jpg
    197.1 KB · Views: 6
  • 2.jpg
    2.jpg
    83.8 KB · Views: 6

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,119
Office Version
  1. 365
Platform
  1. MacOS
will need various rules set-up

looks like you start ar row 6
So select the Range from A6:Z1000 or whatever you range is

Then ADD the following rules

- When the O column shows "Complete", I want to have the whole row in question show green (to show that it has been done)

=$O6 = "complete"
Format green

- When the O column shows "Enquiry sent", I want to have the whole row in question show yellow (to show that an order has been placed)
=$O6 = "Enquiry sent"
Format yellow

- When the O column shows "Incomplete", I want it to follow a series of commands dependant on time left in the N column. (so we know when to place the order)

- If the O column is "Incomplete", I want the colour that is shown in the N Column to be copied over to the rest of the row.
- The N row shows different colours based on the amount of days left to the deadline date.

You wont copy a colour with CF , needs VBA
But you can use the same rules
So what are the Colours and time in N

then you add a new rule with an AND()

= AND( $O6 = "Incomplete" , $N6 = whatever days you want , whatever the calc is )
Format that colour for the days
Provide all the N colours and calculation for time

Now add new rules for the different time and colours in N
 

Dazdup

New Member
Joined
Aug 25, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Brilliant,
Got it working now,

Thanks for your speedy response :)
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,119
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Forum statistics

Threads
1,175,516
Messages
5,897,892
Members
434,685
Latest member
asdf12l3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top