Change color based if cell another sheet are not empty

makis1023

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hello,

I am trying to make a cell change color if a range of cells on another worksheet are not empty

ie cell on worksheet TEST1 that need to change color is E3 and have to check if a range of (lets say B2:C10 cells on Worksheet TEST2 are not empty.

Can this be done with conditional formatting of need VBA?

Cheers
 

makis1023

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
@FormR so this cant be done. right? Its complicated.
Thank you for your effort
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,554
Office Version
  1. 365
Platform
  1. Windows
Hi, post# 5 seems to boil down to this.

I need to check if the range of B2:F6 & B10:F13 at sheet test19 are filled, then change background color of that day at calendar to red.

To which there is a suggested formula to use along with conditional formatting in post# 6 - I've not really followed since what the problem is?
 

makis1023

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
@FormR To make it clear. I am using a calendar, that have the days (in yellow the today() ), that I am using it (the calendar) in order to make new sheets using VBA. When I double click on a day, It creates a sheet based on a template, and name that sheet based the calendar day. At the calendar sheet I have a drop down menu to choose month and year. When I double click a day of that calendar (i.e 1) it creates a new sheet under the name 01-07-2021. At that sheet I have to fill some cells , if all the cells I need to be filled, then the color of the tab turns red. I want and the color for that day at the calendar to be also red (and not yellow as it represents the today() ). I hope you understand what I want to do.
I can upload somewhere the file if you want, but the forum doesnt allow uploads.
Thank you!
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,554
Office Version
  1. 365
Platform
  1. Windows
Hi, does the suggested conditional formatting formula work when applied manually after you have programatically created your sheet?

If it does, can't you just create and apply the rule in the same code that creates the new sheet?
 

makis1023

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

It does. But I cant apply the rule. With a lot of help, coded the excel I am not good at excel. Now, I am trying to learn. Do you want me to upload somewhere the code? Or excel? Can you help me be better?

Thank you
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,554
Office Version
  1. 365
Platform
  1. Windows
Have you tried recording a macro of you manually adding the conditional formatting and using that as a base to build from?
 

makis1023

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

No I havent. My problem is that I create sheets by date. So , and record macro it will not do any help as the name just doesnt stay the same. I think that the conditional formating may work if I put as sheet the variable when I create the sheet. So, in that point I need help. Dont know how to handle that
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,554
Office Version
  1. 365
Platform
  1. Windows
I think that the conditional formating may work if I put as sheet the variable when I create the sheet. So, in that point I need help. Dont know how to handle that

I think so too - which part specifically don't you know how to handle? Why don't you give it a try with the macro recorder and see what you can come up with?

This has moved a long way from the OP (a simple question with a simple answer about CF) and I don't have the time to delve into this new detail - maybe someone else will jump in.
 

makis1023

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Ok thank you for your effort and advices.
Thanks
 

Forum statistics

Threads
1,140,922
Messages
5,703,176
Members
421,280
Latest member
Jaycee01

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