conditional format to change color

J

JOEE1979

Active Member
Joined
Dec 18, 2022
Messages
250
Office Version
  1. 365
Platform
  1. Windows
I have a calendar that takes info from another sheet.
On the info sheet, column "B" changes to green if column "I" has a date.
I would like to replicate that on the calendar if possible and I'm so stuck.
The calendar is LIVE and changes every month.
If you can help me create the conditional format, I would appreciate it.

Thank you

Project tracker.xlsx
ABCDEFGHIJKLMN
12041-Mar-2424461-Mar-2431-Mar-24 YES1
120583471-Mar-2431-Mar-24 YES2
120683621-Mar-2431-Mar-24 YES3
120784651-Mar-2431-Mar-24 YES4
120885181-Mar-241-Mar-241-Mar-2431-Mar-24SAME DAY 5
120901-Mar-2431-Mar-24 YES6
121001-Mar-2431-Mar-24 YES7
121101-Mar-2431-Mar-24 YES8
121201-Mar-2431-Mar-24 YES9
121301-Mar-2431-Mar-24 YES10
121401-Mar-2431-Mar-24 YES11
121501-Mar-2431-Mar-24 YES12
121601-Mar-2431-Mar-24 YES13
121701-Mar-2431-Mar-24 YES14
121801-Mar-2431-Mar-24 YES15
121901-Mar-2431-Mar-24 YES16
122001-Mar-2431-Mar-24 YES17
122101-Mar-2431-Mar-24 YES18
122201-Mar-2431-Mar-24 YES19
122301-Mar-2431-Mar-24 YES20
2024
Cell Formulas
RangeFormula
J1204:J1223J1204=G1204+30
K1204:K1223K1204=IF(IF(COUNTA(H1204,I1204)<>2,"",DAYS360(H1204,I1204,FALSE))=0,"SAME DAY",(IF(COUNTA(H1204,I1204)<>2,"",DAYS360(H1204,I1204,FALSE))))
B1204:B1223B1204=(B604)
G1204G1204=A1204
G1205G1205=A1204
G1206G1206=A1204
G1207G1207=A1204
G1208G1208=A1204
G1209G1209=A1204
G1210G1210=A1204
G1211G1211=A1204
G1212G1212=A1204
G1213G1213=A1204
G1214G1214=A1204
G1215G1215=A1204
G1216G1216=A1204
G1217G1217=A1204
G1218G1218=A1204
G1219G1219=A1204
G1220G1220=A1204
G1221G1221=A1204
G1222G1222=A1204
G1223G1223=A1204
M1204:M1223M1204=IF(AND($L$2>H1204,(ISBLANK(I1204))),"YES","")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4:B7323Expression=AND($L$2>=$G4, NOT(ISBLANK($I4)))textNO
B4:B7323Expression=AND($L$2>$G4, (ISBLANK($I4)))textNO
B4:B7323Expression=AND($L$2=$G4, (ISBLANK($I4)))textNO


Project tracker.xlsx
CDEFGHIJK
2March 2024
3SundayMondayTuesdayWednesdayThursdayFridaySaturday
4252627282912
524642442246124772479244624371
625002450247625882545834725332
726112451249226132589836284003
883172487847183388464846584014
983852523850184728484851885005
1084052585851685418538 85116
1184278459    85177
12       8
13       9
14       10
15       11
16       12
17       13
18       14
19       15
20       16
21       17
22       18
23       19
24       20
Current Month
Cell Formulas
RangeFormula
C2C2=DATE('Calender Setup'!P8,'Calender Setup'!P10,1)
C3:I3C3=C4
C4C4=$C$2-(WEEKDAY($C$2,1)-(start_day-1))-IF((WEEKDAY($C$2,1)-(start_day-1))<=0,7,0)+1
D4:I4D4=C4+1
C5:I24C5=IF(SUMIFS('2024'!$B:$B,'2024'!$G:$G,(CONCAT((TEXT(C$4,"DD-")),(TEXT($C$2,"MMM-YY")))),'2024'!$N:$N,$K5)=0,"",SUMIFS('2024'!$B:$B,'2024'!$G:$G,(CONCAT((TEXT(C$4,"DD-")),(TEXT($C$2,"MMM-YY")))),'2024'!$N:$N,$K5))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C88:I88,C109,C4:I4,C25:I25,C46:I46,C67:I67Expression=MONTH(C4)<>MONTH($C$2)textNO
C88:I88,C109,C4:I4,C25:I25,C46:I46,C67:I67Expression=OR(WEEKDAY(C4,1)=1,WEEKDAY(C4,1)=7)textNO
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
It is a bit tricky without being able to see all the data that the various formulas, including the conditional formatting, refer to but does this work for you?

JOEE1979.xlsm
CDEFGHI
1
2Mar-24
3SundayMondayTuesdayWednesdayThursdayFridaySaturday
4252627282912
52464244224612477247924462437
62500245024762588254583472533
72611245124922613258983628400
88317248784718338846484658401
98385252385018472848485188500
10840525858516854185388511
11842784598517
12
13
Current Month
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C5:I24Expression=LET(r,'2024'!$B:$I,AND('2024'!$L$2>VLOOKUP(C5,r,6,0),VLOOKUP(C5,r,8,0)<>""))textNO
 
Upvote 0
Didnt work,, I would like to post the full file,, but its big, is there a way I can send it to you
 
Upvote 0
JOEE1979 said:
is there a way I can send it to you
Click to expand...
No but you can upload the file to Dropbox, OneDrive, Google Drive etc and provide a publicly shared link here. However, I am not inclined to try and look through a 'big' file that I am unfamiliar with.
Also many helpers choose not to download from unknown sources or are prevented from doing so by workplace security restrictions.

Why not make up a small dummy sample file and post the two relevant sheets with XL2BB?
 
Upvote 0
Peter_SSs said:
No but you can upload the file to Dropbox, OneDrive, Google Drive etc and provide a publicly shared link here. However, I am not inclined to try and look through a 'big' file that I am unfamiliar with.
Also many helpers choose not to download from unknown sources or are prevented from doing so by workplace security restrictions.

Why not make up a small dummy sample file and post the two relevant sheets with XL2BB?
Click to expand...

docs.google.com

Project tracker.xlsx

Calender Setup How to Use This Template Time Entry Step 1:, Enter the Year and Start Month Step 2: ,Choose the Start Day Year,2024 Step 3:,Customize the Theme Colors / Fonts Go to Page Layout > Themes to choose different colors and fonts.,Month,3 Step 4:,Print to Paper or PDF,Start Day of Week,...
docs.google.com docs.google.com

there are 4 sheets that work with each other, hoping the link can help

Thank you
 
Upvote 0
In worksheet 'tester calendar' I have a formula to fin a specific # from worksheet '2024'
Excel Formula: 
=SUMIFS('2024'!B:B,'2024'!G:G,'tester calendar'!$J$2,'2024'!N:N,'tester calendar'!K5)

In worksheet '2024' I have a conditional format to make that number fill the cell RED
Excel Formula: 
=AND($L$2>$G6, (ISBLANK($I6)))

How can I make the cell in worksheet 'tester calendar' mimic what happens in worksheet '2024'

Thank you
 
Upvote 0
To best of my knowledge we can't, so far, create CF rules based on another sheet. So I was thinking of some work around -
  • In any cell on worksheet 'tester calendar' use formula to test the rules on another sheet and it shall return True or False
  • Based on that true or false you can CF any cell in worksheet 'tester calendar'
Check this and revert.
 
Upvote 0
Solution
I use multiple ways to format cells based on data in another sheet. But for that I need to understand -
  1. what these figures in Column B are?
  2. How are you going to use in future - means - some kind of analysis where you need to play around (addition subtraction) with figures?
Because the methods I use is only on cells where data is not used for analysis. And That's IMPORTANT to know
 
Upvote 0
In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.
On this occasion I have merged both threads.
 
Upvote 0
SanjayGMusafir said:
To best of my knowledge we can't, so far, create CF rules based on another sheet. So I was thinking of some work around -
  • In any cell on worksheet 'tester calendar' use formula to test the rules on another sheet and it shall return True or False
  • Based on that true or false you can CF any cell in worksheet 'tester calendar'
Check this and revert.
Click to expand...

I took your advice into consideration, I made another column that would send me a result of BB compared to GG and then used a conditional format based on GG and worksheet 'calendar'.... Then I hid the new column.

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
adding cells based on criteria across several columns
Replies
5
Views
91
Cubist
Cubist
J
  • Question
Transfer from 1 page to another
Replies
1
Views
119
bobaol
B
A
  • Solved
Help Changing Cell Color to Based of Date referring to a specific Interior Cell Color
Replies
8
Views
320
SanjayGMusafir
SanjayGMusafir
V
  • Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
175
kvsrinivasamurthy
K
J
  • Solved
conditional formatting with multiple criteria
Replies
8
Views
75
jbrown021286
J

Forum statistics

Threads
1,215,200
Messages
6,123,611
Members
449,109
Latest member
Sebas8956

Share this page

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
Back
Top