# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

how do you make a cell automatically change colour based on the colour of other cells.ie i want to have a series of cells, perhaps 100, if any of these cells are red i want a seperate cell to be red, once all the red ones have changed colour the red cell can turn green??
Solved by V. C. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 06/07/2018 - 03:27
hii
User 06/07/2018 - 03:27
hi
Excelchat Expert 06/07/2018 - 03:27
how are you doing today
User 06/07/2018 - 03:27
good thanks
Excelchat Expert 06/07/2018 - 03:28
so do you have an excel we can work with?
User 06/07/2018 - 03:28
not one in particular
User 06/07/2018 - 03:28
i just need to know how to do it
Excelchat Expert 06/07/2018 - 03:28
ok based on you description you want to check if a range of cells are red in colour right?
User 06/07/2018 - 03:29
ill show you what i want to do
Excelchat Expert 06/07/2018 - 03:29
so would you be manually filling the cell or would you be using conditional formatting?
User 06/07/2018 - 03:30
the induvidual task name and the main task name is red, as each individual task is finished it gets changed to green, once they are all green the main task name also changes green
Excelchat Expert 06/07/2018 - 03:31
would you manually fill in red color to the induvidual task name or would you use conditional formatting?
User 06/07/2018 - 03:31
the main task to the left automatically changes to green once all the indivdual tasks are changed, theay are changed manually
User 06/07/2018 - 03:32
can be manual
Excelchat Expert 06/07/2018 - 03:32
ok give me a min
Excelchat Expert 06/07/2018 - 03:40
ok using formula there is a work around
User 06/07/2018 - 03:41
ok
Excelchat Expert 06/07/2018 - 03:41
you would have to create a name manager
User 06/07/2018 - 03:41
how do you do that?
Excelchat Expert 06/07/2018 - 03:41
Excelchat Expert 06/07/2018 - 03:42
before doing this select a cell
Excelchat Expert 06/07/2018 - 03:42
C18 for example this cell will be used for calculations and we can hide it later
Excelchat Expert 06/07/2018 - 03:43
once you select c18 cell
Excelchat Expert 06/07/2018 - 03:43
Go Insert>Name>Define and name this formula that you are about to create "col"
Excelchat Expert 06/07/2018 - 03:44
Excelchat Expert 06/07/2018 - 03:44
as you see put this formula in the refer To section
Excelchat Expert 06/07/2018 - 03:44
=GET.CELL(63,!C10)
Excelchat Expert 06/07/2018 - 03:45
Excelchat Expert 06/07/2018 - 03:45
then click ok
Excelchat Expert 06/07/2018 - 03:45
Are you following the steps
User 06/07/2018 - 03:47
hold on
Excelchat Expert 06/07/2018 - 03:47
hello
Excelchat Expert 06/07/2018 - 03:47
sure
User 06/07/2018 - 03:47
trying to find insert to cahnge name
Excelchat Expert 06/07/2018 - 03:48
its on the ribbon
Excelchat Expert 06/07/2018 - 03:48
User 06/07/2018 - 03:52
OK GOT THAT
Excelchat Expert 06/07/2018 - 03:52
ok now in c18 type =col
Excelchat Expert 06/07/2018 - 03:53
type the same formula
Excelchat Expert 06/07/2018 - 03:53
in the matrix of the size of the indivual tasks
Excelchat Expert 06/07/2018 - 03:53
then you would get the colour matrix
Excelchat Expert 06/07/2018 - 03:55
Excelchat Expert 06/07/2018 - 03:55
like this
Excelchat Expert 06/07/2018 - 03:55
so 50 is green and 3 is red
Excelchat Expert 06/07/2018 - 03:56
then select cell a10 (the one with task name in it
User 06/07/2018 - 03:56
IVE DONE SOMETHING WRONG
Excelchat Expert 06/07/2018 - 03:56
ok
Excelchat Expert 06/07/2018 - 03:56
why
User 06/07/2018 - 03:56
YEH IM TRYING TO WORK THAT OUT
User 06/07/2018 - 03:57
CAN YOU SEND THE ACTUAL SPREAD SHEET SO I CAN COPY IT?
Excelchat Expert 06/07/2018 - 03:57
i can send you the link as it wont let me update here
User 06/07/2018 - 03:57
OK
Excelchat Expert 06/07/2018 - 03:59
https://www.dropbox.com/s/vjwcr74gjnashsg/Book1.xlsm?dl=0
Excelchat Expert 06/07/2018 - 03:59
Excelchat Expert 06/07/2018 - 03:59
all i have created is a Name manager and conditional formatting
Excelchat Expert 06/07/2018 - 04:01
you there
User 06/07/2018 - 04:02
YEP
Excelchat Expert 06/07/2018 - 04:02
did you get the file
User 06/07/2018 - 04:03
yep i am just looking at it'
User 06/07/2018 - 04:04
so if i cahnge all the cells to green should the values all change to 50?
Excelchat Expert 06/07/2018 - 04:04
yes
User 06/07/2018 - 04:05
im not sure the formulas have copied over
Excelchat Expert 06/07/2018 - 04:05
click enter on the cell?
Excelchat Expert 06/07/2018 - 04:05
i got it on my end
User 06/07/2018 - 04:06
yeh i tried to copy it in to one of my spread sheets
Excelchat Expert 06/07/2018 - 04:06
did u create a name manager as u see in the excel i sent n the screen shot
User 06/07/2018 - 04:07
yep,
User 06/07/2018 - 04:07
what does the 63 refer to?
User 06/07/2018 - 04:07
=GET.CELL(63,!C10)
Excelchat Expert 06/07/2018 - 04:08
its the code for get function to get the color of the cell
Excelchat Expert 06/07/2018 - 04:09
had u selected cell c18 while getting the new manager
Excelchat Expert 06/07/2018 - 04:13
Hello...
User 06/07/2018 - 04:13
hi
Excelchat Expert 06/07/2018 - 04:14
any progress
Excelchat Expert 06/07/2018 - 04:14
are you able to see how I built the formula?
Excelchat Expert 06/07/2018 - 04:14
This is the only work around for your requirement.. else you would have to writ a VBA code for the same
User 06/07/2018 - 04:16
ok i think i have it
Excelchat Expert 06/07/2018 - 04:16
ok..
User 06/07/2018 - 04:16
now the a10?
Excelchat Expert 06/07/2018 - 04:17
ok click on A10 then click on conditonal formatting
Excelchat Expert 06/07/2018 - 04:17
new rule
Excelchat Expert 06/07/2018 - 04:18
Excelchat Expert 06/07/2018 - 04:18
select use formula to determine which cell to formatt
Excelchat Expert 06/07/2018 - 04:19
enter this formula
Excelchat Expert 06/07/2018 - 04:19
=COUNTIF(\$C\$18:\$F\$23,50)>=24
Excelchat Expert 06/07/2018 - 04:19
click on format and select the green fill
Excelchat Expert 06/07/2018 - 04:20
Excelchat Expert 06/07/2018 - 04:20
Excelchat Expert 06/07/2018 - 04:25
you there
User 06/07/2018 - 04:26
yep
Excelchat Expert 06/07/2018 - 04:26
were you able to get it

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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.