Cell Shading Based on Value, not Conditional Format issue (I don't think)

[banksmi1]

This is what I am looking to do, not sure it this will come across properly in words...

=If(cell shaded tan, do nothing, if(cell value between 0 and 10, change cell shade to yellow, do nothing))

<X<10, do p yellow, to shade cell change nothing), nothing)<><X<10, do p nothing). tan), shading keep yellow, to shade cell change <>These cells are conditionally formatted using the red, yellow, green symbols based on value, I don't want any of that to change if I use this formula/macro.

My cell range is 40 rows by 20 columns that this would need to look at. I am using Office 2010.

I know I can't use formulas to change cell format, I have to use VBA, that is where I could use some help. Thank-you.

 

[Gerald Higgins]

Hi, welcome to the board.

There are several ways to do this in VBA, here's one, not necessarily the best way.

<TABLE style="WIDTH: 83pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=138 border=0 x:str><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=138><TBODY><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" width=138 height=18>Sub Apply_text_based_on_colour()</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>    Dim Cell As Range</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>    Dim Rng1 As Range</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>    Range("G13:G25").Select</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>    Set Rng1 = Selection</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>    For Each Cell In Rng1</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>        Select Case Cell.Interior.ColorIndex</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>        Case 42</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>            Cell.Value = "BLUE"</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>        End Select</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>    Next</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>End Sub</TD></TR></TBODY></TABLE>

This writes the word "BLUE" into cells that are already coloured a certain shade of blue.
You can adapt this to change the colour.

 

[Petard]

Same sort of scenario:

Cells H4:N4 contain one of the values derived from a validated list as follows:

(blank), X, WIP, AP and filled using conditional formatting as (no fill), green, yellow and red respectively.

To understand a little about what I am doing I offer the following. Blank means no value and there is no data. Green (X) means it is complete and work may continue. Yellow (WIP) is a work in progress and is not holding the job up. Red (AP) is awaiting parts and means a work stoppage.

On this detail sheet I want to be able to use cell O4 to find the worst case scenario from H4:N4 and indicate it in O4 then reference that format/value on a roll-up sheet; so if the worst case is "AP" then O4 will indicate either by value or format this condition. I then use that to roll up to the summary sheet.

The order of worst to best is AP, WIP, X, "Blank"

Any ideas?