Chang Color of Cell based on cell text

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
I know that I can do Conditional Formula's but I just have 30 different values that the cell text could be, so I was wondering if there was a way to write that in code? It is also used for 30 different varying locations based on refreshed data.

For an example, in Column A, Rows 2-31, will be different city names, which will vary when the data is sorted each time the revenue's are updated. I wanted the Colors of these cells to change based on what the city name is, but there are too many possibilities to use a conditional formula.

One week it maybe this:

Column A
City X= Black
City Y= Red
City Z= Orange
City A= Yellow
City B= Purple

The next week it could be this:

Column A
City A= Yellow
City X= Black
City B= Purple
City Y= Red
City B= Purple

Does this make sense and can it be done?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
It can be done using a worksheet event probably combined with a select case statement.

Can you give more details?
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
Okay to be exact, let say cell C26=Milwaukee

I want Cell c26 to be Red
and Cell c29 to be red
and Cell c30-c33 to be yellow

but this has to cycle through 30 different city names each with unique colors, and other cells as well, like after it looks in C26, finds the correct color combination, it then goes cell h26, and if cell H26=Portland

I want Cell H26 to be Orange
and Cell H29 to be Orange
and Cell H30-c33 to be Light blue

and then move to the next set of cells, but once I get the code for it to cycle through 2 of them and how to use the color combination, I can cycle through the rest.

Is this possible?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Does it all base itself off of row 26? Give 2-4 good examples of a working color combination. Complete with cell values (case sensitive), and don't forget to mention which cell being updated will trigger this. Point out which cells will be looking at what to change, etc.
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
firefytr said:
Does it all base itself off of row 26? Give 2-4 good examples of a working color combination. Complete with cell values (case sensitive), and don't forget to mention which cell being updated will trigger this. Point out which cells will be looking at what to change, etc.

No, it doesn't all base off of CellC26, but that is the first cell that the city name will change, meaning that cell could be one of thirty different cities.

The cells that will change city names are:
City Name Change will effect color in ell C26,which will effect color in C29 and effect C30 thru C33 Then
City Name Change will effect color in ell C37,which will effect color in C40 and effect C41 thru C43 Then
City Name Change will effect color in ell C48,which will effect color in C51 and effect C52 thru C54 Then
City Name Change will effect color in ell C59,which will effect color in C62 and effect C63 thru C65 Then
City Name Change will effect color in ell C70,which will effect color in C73 and effect C74 thru C76 Then
City Name Change will effect color in ell C81,which will effect color in C84 and effect C85 thru C87 Then
City Name Change will effect color in ell C92,which will effect color in C95 and effect C96 thru C98 Then
City Name Change will effect color in ell C103,which will effect color in C106 and effect C107 thru C109 Then
City Name Change will effect color in ell C114,which will effect color in C117 and effect C118 thru C120 Then
City Name Change will effect color in ell C125,which will effect color in C128 and effect C129 thru C131 Then
City Name Change will effect color in ell C136,which will effect color in C139 and effect C140 thru C142 Then
City Name Change will effect color in ell C147,which will effect color in C150 and effect C151 thru C153 Then
City Name Change will effect color in ell C158,which will effect color in C161 and effect C162 thru C164 Then
City Name Change will effect color in ell C169,which will effect color in C172 and effect C173 thru C175 Then
City Name Change will effect color in ell C180,which will effect color in C183 and effect C184 thru C186 Then
City Name Change will effect color in ell C191,which will effect color in C194 and effect C195 thru C197 Then
City Name Change will effect color in ell H26,which will effect color in H29 and effect H30 thru H33 Then
City Name Change will effect color in ell H37,which will effect color in H40 and effect H41 thru H43 Then
City Name Change will effect color in ell H48,which will effect color in H51 and effect H52 thru H54 Then
City Name Change will effect color in ell H59,which will effect color in H62 and effect H63 thru H65 Then
City Name Change will effect color in ell H70,which will effect color in H73 and effect H74 thru H76 Then
City Name Change will effect color in ell H81,which will effect color in H84 and effect H85 thru H87 Then
City Name Change will effect color in ell H92,which will effect color in H95 and effect H96 thru H98 Then
City Name Change will effect color in ell H103,which will effect color in H106 and effect H107 thru H109 Then
City Name Change will effect color in ell H114,which will effect color in H117 and effect H118 thru H120 Then
City Name Change will effect color in ell H125,which will effect color in H128 and effect H129 thru H131 Then
City Name Change will effect color in ell H136,which will effect color in H139 and effect H140 thru H142 Then
City Name Change will effect color in ell H147,which will effect color in H150 and effect H151 thru H153 Then
City Name Change will effect color in ell H158,which will effect color in H161 and effect H162 thru H164 Then
City Name Change will effect color in ell H169,which will effect color in H172 and effect H173 thru H175 Then
City Name Change will effect color in ell H180,which will effect color in H183 and effect H184 thru H186 Then
City Name Change will effect color in ell H191,which will effect color in H194 and effect H195 thru H197 Then

and if there is no city name in the cell, then to skip it and look at the next one. Once cycle ends return to Range("f4").Select.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

So do you want more of a Conditional Format type of solution, or do you want a solution you can run and do all at once?
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
Most likely run it at once. Will the conditional formula be able to handle 30 different variations, and skip if no city is named?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Well the reason I asked that question, is dependent upon what type of procedure you need. If you want to click a button (or keyboard shortcut) to run this, then you want a Standard Routine (Standard Module); whereas if you want this to 'fire' every time you edit a cell and act like conditional formatting you'll need a Worksheet Event Routine (Worksheet Module). I would think it depends mostly upon you and your future plans for this. Is this a one time deal? If not, you may want to think about using the worksheet change event. If need be, we can do both at the same time; this may be a lot slower and less efficient though.
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
I would like to have a button. I have five other buttons on it already. The data will change about 2 times a week. I won't be editing cells manually, the cells are vlookup formula's based on the revenue data from another sheet, so the city names will be constantly changing, and I figured once all the data was refreshed and recalculated, the city names would shift around, and then I would press this button, and it would color code it based on the city name.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,333
Messages
5,601,008
Members
414,421
Latest member
tonybear1994

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top