# Values-specific coloured cells to retain its colour

#### XanderTheNotSoAwesome

##### New Member
Sheet 1: Has values like 33.878 in Yellow, 17.873 in Red and 96.666 in Green.

Is there a way to ensure that these coloured cells are carried over to sheet 2? (Sheet 2 has a macro that sorts out the scores according to percentile and other considerations so I can’t just copy&paste/use simple filtering or sorting and was wondering if there is a code to bring the colours-specific to its value over instead of manually colouring them)

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### jolivanes

##### Well-known Member
"33.878 in Yellow" to me means in yellow font. Later you say "coloured cells" which to me means cells filled with a color.
Which of the two is it?
You could try
Code:
``````If c.Value = 33.878 Then c.Font.Color = ??????   "<------ whatever colour
ElseIf c.Value = 17.873 Then c.Font.Color = ??????   "<------ whatever colour
ElseIf c.Value = 96.666 Then c.Font.Color = ??????   "<------Whatever colour
End If``````

#### XanderTheNotSoAwesome

##### New Member
"33.878 in Yellow" to me means in yellow font. Later you say "coloured cells" which to me means cells filled with a color.
Which of the two is it?
You could try
Code:
``````If c.Value = 33.878 Then c.Font.Color = ??????   "<------ whatever colour
ElseIf c.Value = 17.873 Then c.Font.Color = ??????   "<------ whatever colour
ElseIf c.Value = 96.666 Then c.Font.Color = ??????   "<------Whatever colour
End If``````
My apologies, I meant coloured cells.

Sheet 1 has hundreds of unique numbers with their own cell colour, is there a way to just tag the value to its cell colour so that it reflects on sheet 2 even if the numbers are rearranged?

#### jolivanes

##### Well-known Member
"even if the numbers are rearranged" in Sheet1 or Sheet2?

Don't quote if not absolutely required. Just a bunch of extra clutter

#### XanderTheNotSoAwesome

##### New Member

Sorry, new to this, thanks for the advice.

So in Sheet 1, it is just a chunk of unsorted data detailing the scores of each student like in the image attached. I did not colour the cells in the image but the values all have their own cell colours. (Green, Orange, Yellow, Red) I do not know how they categorise the colours, it is not based on percentile.

In Sheet 2, after a bunch of macros that I am unable to access (confidentiality so even I am left out of it), they are all sorted out according to which students are in the A band, B band and C band ranked from Top to Bottom.

So Student Z may get bumped up and Student C gets bumped down according to their banding in Sheet 2. And due to some reason, the Sheet 2 that I am given back has their cell colours cleared. I am trying to find a way to colour the cells on Sheet 2 based on how it was coloured in Sheet 1 but have been unable to do so because I do not know the conditions for the cell colours.

Was wondering if there was a code to just tag the cell value to its cell colour in Sheet 1, and bring it forward to use that same code in Sheet 2.
(Eg. Sheet 1: 95.673 cell is Green, 91.636 cell is Orange, 20.787 cell is Red. Would like to have the same exact 95.673 cell to be Green, 91.636 to be Orange, 20.787 to be Red, ultimately a copy paste of their cell colours, in Sheet 2)

#### Attachments

• E5B89A19-179E-4B47-B96B-1023DCC0BD04.jpeg
55.4 KB · Views: 3

#### jolivanes

##### Well-known Member
I assume you know to try it on a copy of the original first.
Code:
``````Sub Maybe()
Dim c As Range
Sheets("Sheet2").UsedRange.Interior.Pattern = xlNone
For Each c In Sheets("Sheet2").UsedRange
On Error Resume Next
c.Interior.Color = Sheets("Sheet1").UsedRange.Find(c.Value, , , 1).Interior.Color
On Error GoTo 0
Next c
End Sub``````

#### XanderTheNotSoAwesome

##### New Member

Yup I am working on a copy of the original, thanks

I will try it when I'm back to work next week Monday

#### XanderTheNotSoAwesome

##### New Member
Hello, I have tried the code but it ended up colouring some of my columns with the colours of my headers instead. Not sure what caused it..
(My headers are black, it coloured some of my columns black.)

On an additional note: some columns that exist on Sheet 1 has been removed on Sheet 2, is that why there’s an issue?

Last edited:

#### jolivanes

##### Well-known Member
If we don't know the range it is guessing work.
Try so.
Code:
``````Sub Maybe()
Dim c As Range
Sheets("Sheet2").UsedRange.Offset(1).Interior.Pattern = xlNone
For Each c In Sheets("Sheet2").UsedRange.Offset(1)
On Error Resume Next
c.Interior.Color = Sheets("Sheet1").UsedRange.Offset(1).Find(c.Value, , , 1).Interior.Color
On Error GoTo 0
Next c
End Sub``````

If you have a particular Range you have to let us know.
In the future, also specify if you have header row(s). Normally you should.

#### XanderTheNotSoAwesome

##### New Member
Understood, will include if I have any more inquiries after

On Sheet 1:

Column A: Serial Number
C: Name
D: Citizen Identity Number
…so on so forth to Column F.
Column G onwards are the subjects.
G: English
H: Math
I: Science
J: History
…until column Q.
Rows can vary from anywhere from 2 to 100 depending on number of students in that semester intake. Their test scores are also filled in accordingly from Column G to Q.

On Sheet 2:

Some columns are removed here:
Unnecessary columns like Address and Citizen Number as well as empty columns (some intake of students are not offered history so they have no history test scores or not offered science, so no science test scores and etc.)

Added columns: Ranking based on their test scores churned out by a macro which I have no access to.

Rows can vary accordingly based on student intake.

Code: it does work this time, but it colours all the cells white, including the student names and personal particulars. Those cells are coloured white even though they were uncoloured under Sheet 1. The cell for the test scores are also white and not the same as their original colour.

As always, really appreciate the help, time and effort you have given to me. Thank you.

Replies
0
Views
37
Replies
5
Views
49
Replies
1
Views
303
Replies
16
Views
80
Replies
4
Views
81

1,133,532
Messages
5,659,365
Members
418,499
Latest member
mbcmel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back