If Statement Based On Text Color

Feb 12, 2007

Is it possible to have a If statement based on the cells text color.

ie

If(color of A2=RED,1,2)

So if Apple text was colored red the result woud be 1
If the Apple text was black the result would be 2

ADVERTISEMENT

IF Statement Based On A Specific Cell Color

Mar 18, 2010

Two part question:

1. How do I structure an IF statement based on a specific cell color (e.g. if a cell is yellow, then perform function x)

2. How do I know what color is what? Is there a pantone reference? A color "name" that excel uses?

View 9 Replies View Related

Color Cell Fonts Based On Text Color Of TextBox Controls On UserForm

Apr 11, 2008

I have got a userform with lots of controls,

One of the action's on a large group of the controls is the same but except for one number

here is an example

If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3
If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3
If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3

This makes a cell that correlates to the textbox red if the text in the textbox is red.

Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop.

Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?

View 3 Replies View Related

Change Text Color Based On Cell Color

Oct 17, 2006

I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white.

i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?

View 5 Replies View Related

VBA Macro - If Statement Based On Length Of Text

Feb 11, 2014

I'm having a bit of trouble with a macro designed to read the length text in a cell and if it = a certain length then perform an action (in this case Text to column)

Here is a small sample of the data I'm working with:

Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<

Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<

Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<

Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<

Tue 02/11/2014
LastBootUpTime

Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
*End Sample*

The text length I want it to perform the action on is highlighted with "<<" if the length of text does not meet the required number then I want the statement to skip and move onto the next one.

I have the text to column code already done with relative references however the long text string I want the statement activated on is not always present which means that the pattern (0,3) is not always consistent.

View 3 Replies View Related

If Statement Showing Text Based On Date Value In A Column

May 14, 2009

I have a worksheet with a range of dates in column E. What I would like is a formula that states If any of those dates is less than or equal to 10 (days) from today and column H text is not "Yes" then show text "Email outstanding Actions" else show nothing?

View 4 Replies View Related

Formulas Based On Text Color

Aug 13, 2014

Tracking Snip.jpg

I have a tracking template to track the milestones of each data sets (received, etl complete, qa, live). In my status column I'm using the following IF formula to auto populate the the status:

=IF(ISBLANK(F3),"Waiting",IF(ISBLANK(G3),"Received",IF(ISBLANK(H3),"ETL Complete",IF(ISBLANK(L3),"QA","Complete"))))

However, I need to now start putting expected dates for these milestones in a grey text for each status. So none of the cells for the above formula will be blank. Is there a way to say if the text is grey, to ignore the date to auto populate the status column based on the black text that is available?

View 1 Replies View Related

Excel 2011 :: Conditional Formatting Based On Text Match From If Statement?

Oct 2, 2013

I want to highlight a cell if the text displayed from an IF formula is equal to the cell content.

CELLS BK10:BN10 are merged and have entered into them the following text "SELL"

Cells BJ20:BP20 are merged and contain the following formula which currently results in the cell displaying "SELL"

=IF(BM22<=-0.08,"SELL", IF(AND(BM22>-0.08,BM22 < -0.03),"NO INDICATION", IF(BM22>=-0.03,"HOLD")))

Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%

When I set up a CLASSIC Conditional Formatting using a formula (="If($BJ$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?

I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.

View 1 Replies View Related

Automatic Text Based On Cell Color

Aug 26, 2008

I would like to have a cell automatically change text based on a conditional formatting formula. When the cell color is red, or the formula is true, then display the word "Expired". Can a formula be devised using results from conditional formatting?

This is being used for expired CPR 2-year certifications. When the certification expires, the cell currently turns red (based on a cell formula) and I would also like the word "Expired" to replace the date.

My condition formula is: =TODAY()>A2 + 730

View 9 Replies View Related

Format Cell Color Based On Text

Jun 24, 2008

On the attached, I want to make the status 3 lines conditional on the text found in another cell.

View 5 Replies View Related

Changing Text Color Based On Information In The Cell?

Nov 28, 2013

I have created a gradebook template so that I can keep track of my assignments as I get them and keep on top of my grades. Any way that I can code excel to work so that if my current average in the class ever drops below 70%, I want the color of the text to change to red to alert me that the grade is too low and needs to come up. I'm new to using VBA in excel and not sure how to do it or what to code.

View 8 Replies View Related

Conditional Formatting Based On Color Or Text Of Another Cell

Jun 14, 2013

1 2 3 4 5 6
1
2 x x x x
3 x x
4 x x
5 x x
6 x x

Assume that is the table i have. In row 1 i need conditional formatting such that if any of the columns contain an x the first row should be color coded. In my above example every column in row 1 will be color coded expect for row 1 column 6 since there are no x's for any any row in column 6.

How can i do this in excel.Also instead of x's if i have manually entered color can we do the same ?

View 1 Replies View Related

Changing Labelcolor In Chart Based On Source Text Color

Mar 5, 2013

I have data. 2 columns a2:b5

each row has a different font color

apple 4
banana 3
peach 2
pear 1

I want the label in a chart to march the font color of the source cell

So in a chart I'd have 4 labels:
"apple"
"banana"
"peach"
"pear"

I can't see how to do it. When i select the albels it's "all or none". I can change ll the labels to a color. But, I want each individual label to be a diferent color.

View 3 Replies View Related

Extract Text In Single Cell Based On Font Color

Aug 5, 2008

I have a cell that has a formula in it like PREM + PRDCMP + ACQ + DLR in a spreadsheet that looks like this:

PREM PRDCMP ACQ DLR VIP COMM TOTAL
2 1 3 4 6 5

PREM + PRDCMP + ACQ + DLR 2 1 3 4 10

where PREM + PRDCMP + ACQ are colored red and + DLR is colored blue. This formula is entered in that particular cell as a text string. I have a formula in the PREM column that says if the word PREM is located in the formula to the left (and it is), then put the value that is located right under the PREM column (2) in the blank cell next to the formula. So, in the above example, I have an IF statement that sees that PREM is in the formula to the left and so underneath the PREM column, next to the formula, it would place the number 2. Now, I need Excel to detect if any word in the formula text string is colored blue and if it is, to put a zero in that column. So, in the formula above, if + DLR is the only text that is blue, I want to have Excel add 2 + 1 + 3 only. I want to have Excel take any string out that might be colored blue, such as + DLR and return the value of zero for it. The purpose of the different colors in the formula is to distinguish which information is relevant and which information should be ignored. So, I'd like Excel to read the above formula and say put the PREM amount in the cell under PREM, put the PRDCMP amount in the cell underneath PRDCMP, put the ACQ amount in a cell under ACQ and put zero in the DLR column since it is colored blue. If any of them (PREM, PRDCMP, ACQ, DLR, VIP or COMM) are colored blue, I want Excel to put a zero in that column.

View 6 Replies View Related

Format Row Cell Color Based On Number Of Text Strings In A Column

Jul 29, 2013

I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.

View 14 Replies View Related

Changing Cell Text Color WITHOUT Using Conditional Formatting Based On Finding Keywords

May 29, 2012

I have a spreadsheet that uses VB macros to calculate sums of cells based on the font color of the numbers inside. It used to be fairly easy going through each cell and "classifying" them by color, so that my macros can go ahead and sum the numbers in each respective color's cell... but now I have a huge amount of numbers and would like to automate the process somewhat. Here's an example using the A and B columns:

flight $400
hotel $150
hotel $130
meal $20
meal $15
flight $350

I tried using conditional formatting to automatically change the color of the adjacent cells based on the presence of a keyword such as "flight" or "hotel", but this change is only cosmetic, and doesn't actually change the font color (it is still the default black, hence why my color-summing macros won't work!).

I'm including a sample macro for what I use to color-sum my cells, but what I am looking to automate the color-coding process based on looking for keywords as explained above in my example. Here is one of the working color-summing macros (for red, in this case) if you'd like to use it as a reference:

Function SumRed(SelectedCells As Range)
' Adds the values of the cells where the font colour is red(3).
Dim Cell As Object
Dim x As Double

[Code] ...........

View 9 Replies View Related

Conditional Formatting - Change Value Font Color In Cell Based On Matching Text?

Jun 26, 2013

Via conditional formatting, I am searching to change the color of a specific word in a cell (not the color of everything in the cell, but only that specific word - and the word repeats in the cell). A function such as =isnumber(search("NOK";A1)) colors all the cell values, which I do not want to do. How i can perform this task?

View 1 Replies View Related

Excel 2013 :: Making Numbers Display With Color Based On Text Inputted In Different Cell?

Nov 20, 2013

EXPENSE MASTER 2013 sample color.xlsx

I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.

View 3 Replies View Related

If Statement Dealing With Color Background

Nov 19, 2009

I am trying to write an If statement that would search a column for cells that have red as a background color, and if they did, would mark the cell with an X.

View 9 Replies View Related

Change Font Color Depended On (if Statement)?

May 1, 2014

how can i change font color depended on (if statement)?

View 1 Replies View Related

Excel 2010 :: Change Color On If And Then Statement

Nov 20, 2013

I tried Conditional format and no luck. (Office 2010)

What I'm trying to do VB is:

If Cell B:B has value of "402" or "up"
and Cell C:C is less >100
Then Change color on Cell C:C to "yello, red, etc" (if C value is <100 no color change)

View 6 Replies View Related

Macro: Changing Color Of Coloumn Header Based On Rows Color

Jan 15, 2009

I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine

Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.

View 8 Replies View Related

Color Column Charts Based On Font Color Data Cells

Oct 27, 2009

I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.

After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.

I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.

View 9 Replies View Related

Setting Color Of Range Based On Adjacent Cell Color

Nov 1, 2009

This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from.

I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors.

The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed.

What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color.

Here is the code I am using to set the color of the cells in Column A:

View 7 Replies View Related

Change Font Color Based On Adjacent Cell Color

Apr 18, 2008

I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.

View 3 Replies View Related

Excel 2013 :: Set One Columns Text To Color Based On Another Columns Results?

Apr 9, 2014

how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.

I'm using Win 8.1, Office 2013.

View 4 Replies View Related

Color Cells Based On Color Of Preceeding Cell

Nov 18, 2007

I am looking for a very simple script that will achieve the following:

On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:

From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.

View 5 Replies View Related

Macro To Color Cells Based On Their Font Color

Jun 18, 2008

I have a spreadsheet that i download from the net daily, which is seperated into columns of information.

I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.

For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.

If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.

I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.

View 9 Replies View Related

Color Cells Based On Adjacent Cells Being Empty & Add Text

Apr 2, 2008

Iím trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. Iím aware of auto conditioning, and Iíve tried to have a play to get this to work, but I just canít work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!

http://img530.imageshack.us/img530/6239/excelspfk0.jpg

View 9 Replies View Related

Font Color Based On Interior Color

Dec 1, 2007

I would like to have cells that conditionally format the font color in the cell based on the background color of the cell. Essentially I have a matrix with some cells highlighted in red, yellow, or orange. There are values in each cell of the matrix. If a value is 0 I want the font to be grey. If the value is NOT 0 AND the cell backgorund color is NOT white, I would like it black and bold. Is there any way to do this in excel or using VB?

View 6 Replies View Related

Conditional Formatting - Cell Color Based On Range Of Cell Color

Aug 9, 2013

Summary of performance of various products against target is as follows,

Product vs Target
Color Code
Result

CH4OH
Green
1.0

[Code] ........

I need the final result automated as follows,

If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red

Is there a way to automate this?

View 8 Replies View Related

Change Text Color When Comparing 2 Text Fields

Aug 20, 2013

How would I compare two text fields (old and new) and change the font in the 3rd column (Difference)?

For example:

Old: I can not figure 3 things out.
New: I can't figure 2 things out.
Difference: can not can't figure 3 2 things out.

View 1 Replies View Related

IF Statement Based On Dec 31??

Nov 30, 2008

Is it possible to use an IF statement based on Dec 31 - of any given year? If Cell B22 contains Dec 31 (year doesn't matter), I need to subtract 1 day from the date in Cell F2. This only pertains to Dec 31. If any other date of the year is in B22, no calculation should take place.

View 3 Replies View Related

Use TextBox Text In If Statement

Jan 18, 2010

I would like to run an 'if' statement but need to perform the logic test on the fly rather than edit the code within the VBE. Example:

View 2 Replies View Related

IF Statement Text Check?

Jul 31, 2014

I need to check if the value in cell A1 contains a certain text, I am using IF(A1="*test*","Y","N") but it does not seem to do the job.

View 2 Replies View Related

IF Statement Based On Format

Feb 17, 2009

Is there any way to do an IF statement that is based on the format of another cell. For example

View 3 Replies View Related

IF Statement Based On Table

Jul 30, 2012

Im trying to put a IF statement together to use the in between function.

I have a table (days) from 1 to 5 ie 21

I have a column of aged purchase orders and im trying to do a IF statement based on the table.

View 5 Replies View Related

If Statement Based On Time Of Day

Mar 3, 2014

I am looking to modify the formula below. Rather than specify the time "TIME(20,55,0)", I am looking to pull the time from another cell within the spreadsheet.

=IF(B6="","",IF(E7-INT(E7)

View 1 Replies View Related

If Statement That Return A String Of Text?

Jan 9, 2009

not sure if an "IF" staement is appropriate?

I have 3 columns containing data:

column A has text in it,
example: A2="red", A3="green", A4="blue"

column B has 3 letter month in it and they could have repeats
example: B2="Jan", B3="Feb", B4="Jan"

C2="Jan"

in D2 list anything column A that matches C2
example: D2="red, blue"

View 11 Replies View Related

Copying Values Into Text Statement

May 29, 2009

I am using Excel 2007.

In cells A1 and B1 I have numerical values, e.g. 50 and 12 respectively. Cell C1 calculates the product A1*B1 (= 600). In another cell, say D1, I want to display a statement like ď50 x 12 = 600Ē with the 50, 12, and 600 taken directly from cells A1, B1 and C1, without re-entering them. If I change the 50 to another value, say 70, then I want D1 to read ď70 x 12 = 840Ē

View 2 Replies View Related

IF Statement To Read Text In A Cell

Dec 29, 2008

I need a statement to look at how many words are in a cell. If there is 4, to return "Stem" to another column, if it's the 5th word or higher then to return the last word in the string.

For Example

Column A.......................................................... .Column B
SOME-TEXT-GOES-HERE....................................../...STEM
SOME-TEXT-GOES-HERE-SOMEMORETEXT.............../...SOMEMORETEXT
SOME-TEXT-GOES-HERE-SOMEMORETEXT-AGAIN...../...AGAIN

I have tried but failed miserably! I though of using a lookup statement but due to the number of variations it would be quite time consuming.

View 8 Replies View Related

ADVERTISEMENT