Question description:
This user has given permission to use the problem statement for this
blog.
Is there an actual function that changes cell colour rather than Conditional Formatting? It wouldn't work in this situation. I basically have an IF statement and I want the TRUE result to be the colour of the cell changing. Ive tried setting it to "1", then setting a rule where all "1" become that colour, but nothing happens. I've tried it with and without the quotations.
Solved by F. J. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
31/05/2018 - 12:02
Welcome, Thanks for choosing Got It Pro-Excel.
User
31/05/2018 - 12:02
Hello! Sorry, was confused how to use the chat
Excelchat Expert
31/05/2018 - 12:02
Don't worry, now here we are.
User
31/05/2018 - 12:02
Is there a function that would work here?
User
31/05/2018 - 12:03
I'm making a function to track the timezones of people. Can I post it in the preview?
Excelchat Expert
31/05/2018 - 12:03
Yeah please do
User
31/05/2018 - 12:04
Basically,it works out the current time in every time zone. So someone just needs to type there name in A and timezone in B, and it displays their time in every zone
User
31/05/2018 - 12:05
Wait, hang on
Excelchat Expert
31/05/2018 - 12:05
Okay.
User
31/05/2018 - 12:05
Well, just realised that I dont need the if statements and can just display the time in each one, so just ignore that
User
31/05/2018 - 12:06
But basically I want to have it so that the person's ACTUAL timezone is highlighted
User
31/05/2018 - 12:06
So, if B = BST, colour C
User
31/05/2018 - 12:07
So a function that just checks if it's the same as the value of BX and sets the colour if it is
Excelchat Expert
31/05/2018 - 12:09
How can we know that the time for the Timezone is correct?
Excelchat Expert
31/05/2018 - 12:10
In cell C2 as a header should it be BST or Time?
User
31/05/2018 - 12:10
BST. It will list all timezones there
User
31/05/2018 - 12:10
so, like:
User
31/05/2018 - 12:11
like that
User
31/05/2018 - 12:11
then the Now function can just be changed by adding/subtracting hours for each timezone
Excelchat Expert
31/05/2018 - 12:11
So we basically need to highlight the correct column for each row.
User
31/05/2018 - 12:11
Yeah, basically.
User
31/05/2018 - 12:12
That would be the ideal
User
31/05/2018 - 12:12
So that you can see that person's acrual timezone in the list more easily
User
31/05/2018 - 12:14
So is there a function I can use to change the cell colour?
Excelchat Expert
31/05/2018 - 12:15
Yeah,
Excelchat Expert
31/05/2018 - 12:15
In fact conditional formatting can work.
User
31/05/2018 - 12:15
How?
Excelchat Expert
31/05/2018 - 12:15
Let me formulate it for you.
User
31/05/2018 - 12:15
Okay, ty!
User
31/05/2018 - 12:20
You found something? Only 1 minute left D:
Excelchat Expert
31/05/2018 - 12:20
Allow the time extension.
Excelchat Expert
31/05/2018 - 12:20
Yeah I am almost done.
User
31/05/2018 - 12:20
How? i think I canceled it
Excelchat Expert
31/05/2018 - 12:21
You should have not.
Excelchat Expert
31/05/2018 - 12:21
Even though it ends, you will still get the results.
User
31/05/2018 - 12:21
Oh, okay. And then I can add my card details whenever and get more sessions?
Excelchat Expert
31/05/2018 - 12:22
Yeah
Excelchat Expert
31/05/2018 - 12:26
The formula used is:
Excelchat Expert
31/05/2018 - 12:26
=IF(INDIRECT(ADDRESS(ROW(),COLUMN($B$1)))=INDIRECT(ADDRESS(ROW($A$2),COLUMN())),1,0)
Excelchat Expert
31/05/2018 - 12:27
Go to conditional formatting and then choose new rule.
Excelchat Expert
31/05/2018 - 12:27
In the formula box, enter the formula I have given you.
Excelchat Expert
31/05/2018 - 12:27
Ensure to change the range where it is being applied so that it matches with you data.
Excelchat Expert
31/05/2018 - 12:28
$A$2 - This implies the row of the data with column headers.
Excelchat Expert
31/05/2018 - 12:28
$B$1 - The column of Timezone
Excelchat Expert
31/05/2018 - 12:29
Ensure they have absolute reference.
Excelchat Expert
31/05/2018 - 12:29
Remember the formula is applied after selecting the whole data which you want to apply the conditional formatting.
Excelchat Expert
31/05/2018 - 12:30
Next time ensure you accept time extension prompt in your screen so that you can be able to chat with us.
Excelchat Expert
31/05/2018 - 12:30
If this does not solve the problem, kindly let us know. Thank you.
Excelchat Expert
31/05/2018 - 12:30
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help. Enjoy the rest of your day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
