**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.*