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
[Uploaded an Excel file]
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
[Uploaded an Excel file]
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
where C10 would be where your indivual tasks starts
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
[Uploaded an Excel file]
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
[Uploaded an Excel file]
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
[Uploaded an Excel file]
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
please download this
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
[Uploaded an Excel file]
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
[Uploaded an Excel file]
Excelchat Expert
06/07/2018 - 04:19
click on format and select the green fill
Excelchat Expert
06/07/2018 - 04:20
[Uploaded an Excel file]
Excelchat Expert
06/07/2018 - 04:20
[Uploaded an Excel file]
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.
