Question description:
This user has given permission to use the problem statement for this
blog.
I need a conditional formatting formula to change the color and text of a cell in row 2 based on a numerical value in row 1.
Solved by E. L. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
23/08/2018 - 08:36
Hi
User
23/08/2018 - 08:36
hello
Excelchat Expert
23/08/2018 - 08:36
It's pleasure to help you today.
Excelchat Expert
23/08/2018 - 08:36
You wan to apply the conditional formatting in row 2 based on row 1?
User
23/08/2018 - 08:36
yes
Excelchat Expert
23/08/2018 - 08:37
Please provide sample info in the preview.
User
23/08/2018 - 08:37
I want to change the color and text of a cell in row B based on a value in row 1
Excelchat Expert
23/08/2018 - 08:37
Sure
User
23/08/2018 - 08:40
I want the color and text in cell D7 to automatically show up in B5 based on cell A3
User
23/08/2018 - 08:41
So basically I want "Bronze" to be correlated with "3" and to be able to enter 3 in Row A and Bronze to automatically show up in Row B
Excelchat Expert
23/08/2018 - 08:41
Ok.
Excelchat Expert
23/08/2018 - 08:42
Can i add simple modifications to the sheet?
User
23/08/2018 - 08:42
yeah
Excelchat Expert
23/08/2018 - 08:43
I am adding the numbers to the table.
Excelchat Expert
23/08/2018 - 08:44
So, based on the number in column A, the material will change in column B
User
23/08/2018 - 08:44
okay what if i wanted to do 1-5 be Bronze and then 6-10 be silver, 11-15 be Gold, and 16-20 be Platinum
Excelchat Expert
23/08/2018 - 08:45
You have to change the condition to true and change the numbers. I will show it in the preview.
User
23/08/2018 - 08:46
Please do
User
23/08/2018 - 08:46
the color in Row B hasn't changed though
Excelchat Expert
23/08/2018 - 08:46
It is through conditional formatting. I can apply to it.
User
23/08/2018 - 08:46
yes please
Excelchat Expert
23/08/2018 - 08:49
Please refer I and J columns
Excelchat Expert
23/08/2018 - 08:49
I col: Input column.
Excelchat Expert
23/08/2018 - 08:50
J is the result column.
Excelchat Expert
23/08/2018 - 08:50
I am using similar formula and changing false to true in the formula.
Excelchat Expert
23/08/2018 - 08:50
True gives the approx match. and it provides exactly based on the range.
Excelchat Expert
23/08/2018 - 08:51
This can be used without going for if and else statements.
User
23/08/2018 - 08:51
Okay so can you apply this formula to Row J and I so that the colors change please
Excelchat Expert
23/08/2018 - 08:52
Yes. I am applying the colours now.
User
23/08/2018 - 08:52
Column J and I
User
23/08/2018 - 08:52
i have a data sheet of 400 rows and want to be able to put in 6 and the next row say "silver" and be silver too
User
23/08/2018 - 08:54
Here is the sheet im working with
[Uploaded an Excel file]
Excelchat Expert
23/08/2018 - 08:54
I have updated the colours.
Excelchat Expert
23/08/2018 - 08:54
in column B.
User
23/08/2018 - 08:54
can you do it for this sheet
Excelchat Expert
23/08/2018 - 08:55
I am checking the file.
Excelchat Expert
23/08/2018 - 08:55
I have opened the file.
User
23/08/2018 - 08:55
i want the data in the Column "Products" to make the color adjacent to it in Column "Level" correspond with the numbers and color/text to the right
Excelchat Expert
23/08/2018 - 08:56
Ok. You want to colour the cells. And no need to have text like Platinum / Gold etc?
User
23/08/2018 - 08:56
Both
Excelchat Expert
23/08/2018 - 08:57
Where do i need to add formula for the text?
Excelchat Expert
23/08/2018 - 08:57
Is it column E?
User
23/08/2018 - 08:57
I want the cell in Column "Level" to say Gold and be Gold if the number in Column "Products" is between 23 and 34
User
23/08/2018 - 08:57
yes column E
Excelchat Expert
23/08/2018 - 08:57
Ok. Thank you. I am working on it.
User
23/08/2018 - 09:03
hows it goin
Excelchat Expert
23/08/2018 - 09:04
I have updated the formula and colours. Please check the file.
[Uploaded an Excel file]
User
23/08/2018 - 09:04
youre a god-send
User
23/08/2018 - 09:05
thank you
Excelchat Expert
23/08/2018 - 09:05
You are welcome.
Excelchat Expert
23/08/2018 - 09:05
Thank you.
Excelchat Expert
23/08/2018 - 09:05
Please visit Got IT Pro. Have a great day ahead.
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.
