I have a table in sheet1 with some cells and fonts are color formatted. Another similar table in sheet2 has the IF function to get one of its column contents if J3 cell matches (Data Validation list) the column heading in sheet1. Thanks to the formula =IF($J$3=Sheet1!$A$3,Sheet1!A19,"")&IF(..... because it works in getting the column values, however the color format of the fonts in sheet1 are not applied in sheet2 automatically. All I want now is to get the same cell font colors from a column in sheet1 to a column in sheet2 if J3 changes. Appreciate the kind assistance from anyone please.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Applying the same cell color format from sheet to another
- Thread starter Teororo
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,265
- Office Version
- 365
- Platform
- Windows
Welcome to the Board!
How exactly are these cells being colored?
If they are being colored by Conditional Formatting, then just set up the same Conditional Formatting rules in Sheet2.
If they are being colored manually, then the only way I can think of is to use VBA.
How exactly are these cells being colored?
If they are being colored by Conditional Formatting, then just set up the same Conditional Formatting rules in Sheet2.
If they are being colored manually, then the only way I can think of is to use VBA.
Upvote
0
Thansk you for
The cells are colored manually. The contents of column cells are texts and have two or three different colors.
I agree that VBA may help. Any VBA code that you may want to share. Thanks and regards.
Warm Greetings and thank you for a warm welcome.
The cells are colored manually. The contents of column cells are texts and have two or three different colors.
I agree that VBA may help. Any VBA code that you may want to share. Thanks and regards.
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,265
- Office Version
- 365
- Platform
- Windows
Can you post an example of your two sheets, so we can see the structure, and exactly how this should look/work?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Upvote
0
Thanks again and this is the first sheet
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I4:I57 | I4 | =IF($J$3=Sheet1!$A$3,Sheet1!A4,"")&IF('KIT Academic Calendar'!$J$3=Sheet1!$B$3,Sheet1!B4,"")&IF('KIT Academic Calendar'!$J$3=Sheet1!$D$3,Sheet1!D4,"")&IF($J$3=Sheet1!$C$3,Sheet1!C4,"") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| I4:K4 | Expression | =IF($J$3=Sheet1!$A$3,Sheet1!$A$4="True") | text | NO |
| J3 | Cell Value | ="Terms" | text | NO |
| J3 | Cell Value | ="Public Holiday" | text | NO |
| J3 | Cell Value | ="Admin" | text | NO |
| J3 | Cell Value | ="ALL" | text | NO |
| D8 | Cell Value | =TODAY() | text | NO |
| H42 | Cell Value | =TODAY() | text | NO |
| H6 | Cell Value | =TODAY() | text | NO |
| D19:G19 | Cell Value | =TODAY() | text | NO |
| D26:H26 | Cell Value | =TODAY() | text | NO |
| D20:H20 | Cell Value | =TODAY() | text | NO |
| D42:G42 | Cell Value | =TODAY() | text | NO |
| E5 | Cell Value | =TODAY() | text | NO |
| D4:H4,D5,F5:H5,H19,D6:G6,D7:H7,D21:H25,D27:H41,D43:H57,D9:H18,E8:H8 | Cell Value | =TODAY() | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| J3 | List | All,Public Holiday,Terms,Admin |
Upvote
0
This is the second sheet:
| KIT Calendar 2023_draft2_101022.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 3 | ALL | Public Holiday | Terms | Admin | ||
| 4 | 28-30 Dec: Compulsory Closure | 25Dec: In Honour of Christmas; 26Dec: In honour of Boxing Day | ||||
| 5 | 2Jan: In honour of New Year 2023; 3Jan: All Campuses Open | 2Jan: In honour of New Year 2023; | ||||
| 6 | 13Jan: Non Starters last day for fee full refund | 13Jan: Non Starters last day for fee full refund | ||||
| 7 | ||||||
| 8 | 23Jan: All Lecturers of all Campues return | |||||
| 9 | 30Jan: All Campuses Start | 30Jan: All Campuses Start | ||||
| 10 | ||||||
| 11 | ||||||
| 12 | ||||||
| 13 | ||||||
| 14 | ||||||
| 15 | ||||||
| 16 | 20Mar: Mid Year App. Opens Betio & Christmas | 20Mar: Mid Year App. Opens Betio & Christmas | ||||
| 17 | ||||||
| 18 | 6Apr: Term 1 Ends; 7Apr: Health Day | 7Apr: Health Day | 6Apr: Term 1 Ends | |||
| 19 | 14Apr: Good Friday | 14Apr: Good Friday | ||||
| 20 | 17Apr: Easter | 17Apr: Easter | ||||
| 21 | 24Apr: Term 3 Starts | 24Apr: Term 3 Starts | ||||
| 22 | 1May: In Honour of Labour Day | 1May: In Honour of Labour Day | ||||
| 23 | 12May: Application closes midyear intake Christmas campuse | 12May: Application closes midyear intake Christmas campuse | ||||
| 24 | 15May: 2024 Application Forms to O/I; 18May: Midyear Test | 15May: 2024 Application Forms to O/I; 18May: Midyear Test | ||||
| 25 | ||||||
| 26 | ||||||
| 27 | ||||||
| 28 | 12Jun: 2024 Intake Application opens; 16Jun: Xmas midyear names up | 12Jun: 2024 Intake Application opens; 16Jun: Xmas midyear names up | ||||
| 29 | ||||||
| 30 | 30Jun: Term 2 Ends | 30Jun: Term 2 Ends | ||||
| 31 | 3Jul: Semester break All Campuses; 7Jul: In honor of Gospel Day | 7Jul: In honor of Gospel Day | ||||
| 32 | 10-12Jul: Ind Anniversary; | 10-12Jul: Ind Anniversary; | ||||
| 33 | ||||||
| 34 | 24Jul: All Campuses Semester 2 Starts | 24Jul: All Campuses Semester 2 Starts | ||||
| 35 | 3Aug: KIT Open Day; 4Aug: National Youth Day | 4Aug: National Youth Day | ||||
| 36 | 11Aug: 2024 Intake Applications close | 11Aug: 2024 Intake Applications close | ||||
| 37 | ||||||
| 38 | ||||||
| 39 | 1Sep: Put up Shortlisted applications | 1Sep: Put up Shortlisted applications | ||||
| 40 | ||||||
| 41 | ||||||
| 42 | 22Sep: End of Term3 | 22Sep: End of Term3 | ||||
| 43 | 26, 27Sep: Intake Test; 28,29Sep Marking; Betio MidSem Break | 28,29Sep Marking; Betio MidSem Break | 26, 27Sep: Intake Test; 28,29Sep Marking | |||
| 44 | 2Oct: Term 4 Starts; 4Oct: World Teachers Day; 5Oct: QTLC | 2Oct: Term 4 Starts; | ||||
| 45 | 13Oct: SLT | |||||
| 46 | 16-20Oct: Internal Audit | |||||
| 47 | ||||||
| 48 | 2Nov: Staff English Test | |||||
| 49 | ||||||
| 50 | 17Nov: 2024 Student List Published | 17Nov: 2024 Student List Published | ||||
| 51 | ||||||
| 52 | 1Dec: All Campuses End of final Term/Semester; | 1Dec: All Campuses End of final Term/Semester; | ||||
| 53 | ||||||
| 54 | 11Dec: Human Right; 13Dec: Bridg Grad; 15Dec: Trades&NonTrad Grad | 11Dec: Human Right; | ||||
| 55 | 18Dec: Last day for staff; 22Dec: Abemama Grad | 18Dec: Last day for staff; 22Dec | ||||
| 56 | 25Dec: Christmas; 26Dec: Boxing Day | 25Dec: Christmas; 26Dec: Boxing Day | ||||
| 57 | 1Jan: New Year 2024; 2Jan: All Campuses Open | 1Jan: New Year 2024 | ||||
Sheet1 | ||||||
Upvote
0
