Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Change Font Color Based On Adjacent Cell Color


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Change Font Color If Cell Color Become Yellow (6)
I have font color white in blank cells in column E and I (from row 5 to row 245) so the visitors will not see the text. If any of these cells become yellow (color code is 6), the font color will become black so visitors can see the text alot far better than white. I've tried this code myself after this post but nothing happen

View Replies!   View Related
Setting Color Of Range Based On Adjacent Cell Color
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 Replies!   View Related
Change Font Color Based On Conditions
I'm trying to get the font to turn red in column G if it is greater than or equal to column I, and get it to turn orange if it is between 80% of I and I. This is what I've got so far.

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("G" & Target.Row) >= Range("I" & Target.Row) Then Range("G" & Target.Row).Font.ColorIndex = 3
If Range("G" & Target.Row) >= Range(("I" & Target.Row) * 0.8) Then Range("G" & Target.Row).Font.Color = 45
End Sub

View Replies!   View Related
Changing Font Color Based On Change From Previous Value
I want the order size to be colored red. If the most recent order is larger than the previous order, I want the order size to be colored blue. If the most recent order is the exact same size as the previous order, I want the order to be colored black.

How would I make code that would let me "remember" the previous order size and the most recent order size, and let me compare them to conditionally format the font?

View Replies!   View Related
Color Column Charts Based On Font Color Data Cells
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 Replies!   View Related
Change Font Color In A Range Based On One Cells Information
I have attached an example file.. Basically I want the text in a range to change to red if one cell="Description".

Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link"

The example file explains it a lot better..

Thanks for the help.. Sorry if this may seem like a duplicate post but I was not able to get any of the examples I searched for to work that others have posted.. Also, maybe conditional formatting would be an option here but I could not get it to work as it will only meet the first condition that comes across thats true...

View Replies!   View Related
Macro To Color Cells Based On Their Font Color
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 Replies!   View Related
Change Text Color Based On Cell Color
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 Replies!   View Related
Change Cell And Font Color Using VBA
This appears to be so simple, I've searched the forums and have found almost the right VB code but not quite.
I have a 3000 row spreadsheet with various columns.
Column M can either be PASS, FAIL, REJECTED, IN PROGRESS or NOT ASSESSED.
I need the rows (A to M inclusive) to change colour and text colour dependent on this condition.

I almost had an answer with Select.Case but I couldn't get the formula to work with text, only numbers.


View Replies!   View Related
Font Color Based On Interior Color
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 Replies!   View Related
Change Font Color If Another Cell Is Blank
I have a table - the first column is quantity, the others are descriptions, prices, etc.

I need to change the font of the other columns to white if the quantity cell is empty.

Jennifer Auto Merged Post;I'm still working on this, making baby steps. This is my code so far:

If Range("B26") = IsBlank Then
Range("C26:K26").Select
Selection.Font.ColorIndex = 2
End If

It works for line 26. Can someone give me a hint about how to make it do the same things for lines 27 through 62? Without having 35 IF statements in my code?

View Replies!   View Related
Conditional Formatting :: Change Font Color Of Cell
Cell B157 is changed by checkbox 1= TRUE, 0 = FALSE
Cell H1 and Cell H2 are percentages changed by the user.

Range(AI8:AQ8,AI12:AQ12,AI16:AQ16,AI20:AQ20, etc) each cell in the range contains a formula, and is protected from the user.

I need the contents of each cell in the range to change font color (independent of each other) when the formula of any of the cells within the range changes - to the following

If B157 = 1

Cells < H1 (a percentage changed by user), would turn Red
Cells > H1 but < H2 (a percentage changed by user) would turn Yellow
Cells > H2 would be Green

If B157 = 0

Cells < H1 (a percentage changed by user), would turn Green
Cells > H1 but < H2 (a percentage changed by user) would turn Yellow
Cells > H2 would be Red

I need it to fire whenever the value of one of the cells in the range changes.

I have tried If statements, Select Case, and a combination of both but just can't get it!

View Replies!   View Related
Formula Or Macro To Change Font Color For Each Cell In Range
I have a spreadsheet that contains the 5 digit numbers in the rows and the columns respecstively. I'd like a formular or macro to change font color for each cell. If the combined value of the 5 rows are greater or less than the combined range 87030 and 87200, the 5 cells will be changed to Red. If:.........

View Replies!   View Related
Conditional Cell Locking Based On Font Color
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?

More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?

View Replies!   View Related
Extract Text In Single Cell Based On Font Color
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 Replies!   View Related
CHANGE Font Color
try explaning this again....Also conditional formatting will not work...
Looking for a formula in excel to do this---
I have a lottery numbers system in the 3 digit and 4 digit
numbers...In row A we have the 3 digit that were drawn about
3000 ---3 digit numbers and in row C we have also 3000 numbers
for the 4 digit ....All we need to do is Highlight All the
even numbers and make them RED font---in the 3 and 4 digit rows...
Is there a way to do that?????
A--row-----C--row
123---------4429--make the 2red in the 3 digit row and 442red
in the 4 digit row.
033---------5579--nothing in this row
229---------2200---etc....

View Replies!   View Related
To Change The Color Of Font
I use the "custom header&footer", and I want to to change the color of font, but I don't finde this choice!

View Replies!   View Related
Change Font Color ...
I would like to address this:

If cell in column1 is filled (.Interior.ColorIndex = 15) then font color is 0 in any cell of that row.

(right now the color is red and I cannot change this at the source but will have to add a sheet change event maybe)

View Replies!   View Related
Change Font Color Of Last Row
I am wanting to be able to find the last row of data (range changes on a monthly basis) and change the colour of the font to white for the entire row. I don't want to delete the row as contains totals but want the font not visable.

View Replies!   View Related
Formula To Change Font Color
I am trying to figure out what the formula would be to change the font color if the entered number is less than the quota.

I'm making a spreadsheet for work that each of the reps will punch in their daily numbers, and, for example, if there quota was 70 and they are <70 than the font will turn red, and if greater or equal to 70 it will turn green.


View Replies!   View Related
Change Color Font Depending In My If Then Else
I want change color Font depending in my if then else.

I have workbook with several sheets, but i need change colorfont Is that validation is true.

The number are in Sheets("PSDATA").Range("AA")

Private Sub color() .....

View Replies!   View Related
Using IF Function To Change Font Color
I'm trying to use Excel to keep team standings for a baseball board game. I had no problem calculating wining percentage. Finally figured out how to calculate 'games behind.' Now I'm wondering if there is a way to change the font color of a team name based on their wining pct?

A typical row contains: A2(Team Name), B2 (Wins), C2 (Losses), D2 (Pct (B2/(B2+C2)), E2 (GB). Is there a way to use the IF function to return the 'Team Name' in green if D2>.500, 'Team Name' in red if D2<.500, and 'Team Name' in black if D2=.500 ??

View Replies!   View Related
Macro To Change Font Color
macro that will change the cell color based on text? I have a list of titles in column B. Everytime the word 'Car' is found in this column, I would like to change the font color in columns C-G to the color white within the same row.

View Replies!   View Related
Font Color Change Not Sticking
I have a user that was recently upgraded to Excel 2003. He has a problem when changing font color in a cell. Anytime he changes the color of the font to red and then clicks another cell the color reverts back to Black. Now when he clicks on the cell it displays the color red in the formula bar but no in the cell. it also displays the correct color in hte print preview screen. this happens no matter what Excel file he is wokring in.

View Replies!   View Related
Change Font Color While Typing
Is there a macro or code snippet that can change the color of the font while i am typing in the cell. Means that if the font color int he cell is originally RED, i can type and change it to another color using VB code.

View Replies!   View Related
Conditional Formatting Change The Font Color
Using conditional formatting I can change the font color (to blue) of a cell if it meets a certain criteria. Now what I would like is to display a message when the color changes to blue. The cell range is E26 to E40.

View Replies!   View Related
Change Font Color Of Command Buttons
I have this code for 10 Command Buttons to change the font colour. This is part of a much larger piece of code, but to simplify it, I am just using this
Sub ForeColour()
Sheet1.Cat1.ForeColor = &HC0&
Sheet1.Cat2.ForeColor = &HFF0000
Sheet1.Cat3.ForeColor = &HFF0000
Sheet1.Cat4.ForeColor = &HFF0000
Sheet1.Cat5.ForeColor = &HFF0000
Sheet1.Cat6.ForeColor = &HFF0000
Sheet1.Cat7.ForeColor = &HFF0000
Sheet1.Cat8.ForeColor = &HFF0000
Sheet1.Cat9.ForeColor = &HFF0000
Sheet1.Cat10.ForeColor = &HFF0000
End Sub

In Excel 2003, this code works instantaneously, but I am trying to run the same code in Excel 2000 and it takes 10 seconds. Is there any way that I can speed this up in Excel 2000?

The original file was created in Excel 2003, but I have created a new file in Excel 2000 with the 10 buttons on it and the code and it still takes 10 seconds.

View Replies!   View Related
Change Font Color In Print Footer
I wrote a macro to do me page footers

Sub EditFooter()
With ActiveSheet
.PageSetup.CenterFooter = "Page &P of &N"
.PageSetup.RightFooter = "Préparé le " & Format( Date, "dddd, dd MMMM yyyy")
End With
End Sub

it works fine, howver I would like to change the font color to a light grey, and I have no clue how to do that.

View Replies!   View Related
Change Default Text/Font Color
I was just wondering how I can type in a different colour. I.e. I have a document and I want to type in random cells but I always want to be typing in red, or blue etc. Hoe can I change the default text color?

View Replies!   View Related
Auto Calculate Color Function On Cell Color Change
I would like to be able to change the color of a cell in V4:AB31 and have the formula in AM10:AM13 automatically calculate the new result. As it is now the user has to press Ctrl ALT f9 for the formula to recalculate.

View Replies!   View Related
Conditional Formatting Change The Font Color And Look Blanks
I have a simple sheet that has columns I-K. My issue is that in column K I am dividing J/I. If both J and I cells have a "0" then I get the #DIV0 error. If both J and I cells are blank then I get the #VALUE! error. I need it so that if the cells in J and I have a zero in them then it places a 0 in K. But if both J and I are blank then it puts something else that I can have Conditional formatting change the font color so column K looks blank. I think the IF formulas I am try to use recognize blank cells as zeros as well.

View Replies!   View Related
Fill Color And Font Color No Longer Work
For some reason the fill color and font color no longer work! If I set the font color as red, it continues to show up as black!

This is the strangest thing! I have no conditional formatting set and this just started a week ago.

View Replies!   View Related
Change Cell Color Based On Value
I'm putting the sum of 3 cells in a 4th cell(D2). If the sum is greater, I would like the sum to be the color green. If the sum is less, I would like the sum to be the color red. Is this possible?

(D2) has change value, I want to compare the new value against the existing one.

View Replies!   View Related
Change The Tab Color Based On A Cell Value
I'm trying to change the tab color based on a cell value. This I can do, but only using a Change Event with code in the Worksheet section. I would like it to update more instantly and without the need for a Change on the sheet to occur. So I was thinking a Worksheet_Activate event, or perhaps a Workbook_Activate event, but i can't get it to work.

For further info, if not confusing, the cell referenced in code below (on the Dan sheet) is filled using a user entered variable when the workbook is opened. Depending on this value, i would like the tabs to update their color accordingly and instantly, as well as the color updating if the cell B3 on the sheet is changed. Perhaps I need a Worksheet Activate & Deactivate and Workbook Activate and Deactivate?

Public sPeriod As Long
Private Sub Worksheet_Change(ByVal Target As Range)
sPeriod = Worksheets("Dan").Cells(2, 4).Value
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "B3" Then
Select Case UCase(Target.Value)
Case sPeriod - 1: ActiveSheet.Tab.Color = RGB(0, 0, 255)
Case Else: ActiveSheet.Tab.Color = RGB(255, 0, 0)
End Select
End If
End Sub

View Replies!   View Related
Count By Font Color & Background Color
In excel 2000, I am trying to count the lines in a spreadsheet that has the first cell in each row highlighed in yellow. Additionally, I am trying to count the number of rows that has a red font used in the cell in column D.

I have reviewed other posts by searching counting highlighted cells, but I do not understand how to complete the entire process. One of the suggestion was to create a vb script in combination with using =cellindex(??). I do not really understand how to create this, name it and the run it.

View Replies!   View Related
Color Cell Fonts Based On Text Color Of TextBox Controls On UserForm
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 Replies!   View Related
Count Cells Based On Font Color
Hi, I've been cracking my head for a few days your assistance is much appreciated.

Name SA1SA2SA3SA4SA5AYESNONOYESYESBYESNONOYESNOCYESNONOYESNODYESNONOYESYESEYESNONOYESYESFYESNONOYESNO

The ones in red is conditionally formatted. So I want at the end of each row to count how many are in red.


View Replies!   View Related
Deleting Cells Based On Font Color
I am trying to delete certain cells within a range of data (A13:E29) that have white font or #N/A's. The only information I need is the black data. I have to manually go and clear these cells for many different ranges. I'd like to incorporate a macro that analyzes the range first to delete the values that are white font or #N/A.

View Replies!   View Related
Change Cell Color Based On Number
Can I change the color of a cell based on my deviation from the number based above it? I am making a golf spreadsheet to base my performance off of, and I was wondering if I could make the cell change color based on my performance on the hole (different color for how many strokes above or under par). If anyone has any idea on this issue, please let me know.


View Replies!   View Related
Change Color Of Cell Based On Date
In an excell worksheet I need to change the color of a cell within a column based on the date entered. For example, if the date in the cell is 60 days from today's date then the cell should be green, if the date in the cell is 90 days from today's then the cell should be blue, if the date in the cell is 120 days from today's date then the cell should be yello.

View Replies!   View Related
Change The Fill Color Of A Cell Based On Its Value
I'm using Excel 2003- and I am trying to change the fillColor of a cell based on the value of that cell.

11111122222222233333333344455555555555556666777888

If I use Conditional Formatting I can only use 3 colors, I'd like to use 8 or 9. Is there another way to do this without using the Conditional Formatting?

I'd like the result to look something like this-

11111122222222233333333344455555555555556666777888

I've looked at the similar threads in this Forum, but I couldn't find exactly what I needed.

View Replies!   View Related
Sorting Recording Based On Background Or Font Color?
Is it possible to sort the excel records based on background or font color?

View Replies!   View Related
Color Cells Based On Color Of Preceeding Cell
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 Replies!   View Related
Using 'IF' With Conditional Format (change Cell Color Based On The 'P' Or 'F')
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters.
Samples of what I've tried:
=IF(LEFT(L23,1)=P)
=IF(MID(L23,1,1)=P)
etc.

I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?

View Replies!   View Related
Change Cell Background Color Based On Data?
I want to change the color of a cell's background based on valid data in the cell. I have a pull-down list, it uses a named list, and this all works fine. I want the cell to be red, and then after the user selects a valid entry from the pull-down list, I want to change the cell to yellow.

View Replies!   View Related
Highlight Or Change The Color Of A Cell Based On The Date
I looked around at some date functions and could not find one to work.

I have a sheet that has 365 days. When I load the sheet I want to cell for today to be highlighted in some way..... color or just active.

If date = 05-10-2007 then
BackgroundColor = 17
else
BackgroundColor = 9
end if

View Replies!   View Related
Change Color Of All Cells On Sheet Based Off Of One Cell Value
I want to change the fill color of all cells on my sheet, based on the value of one specific cell. In my sheet, I am using cell F1 as the trigger for the change. If the word Blue is in the cell, I want the background color of all cells to be Blue. Likewise for Red and Yellow as well. I don't believe conditional formatting can get this done, as all but the one cell (for this) will be empty.

View Replies!   View Related
Change Bubble Chart Color Based On Cell Value
Let Sheet 1 have data in 3 columns to support a standard bubble chart

Col A: X Values
Col B: Y Values
Col C: Bubble Size

Id like to be able to color the bubbles according to the Y values, whether the Y values fall within the following ranges

Green for Y <=2
Orange for 2<Y<=5
Red for Y > 5

Im assuming some straightforward VB code is in the works but Im not quite there yet

View Replies!   View Related
Conditional Formatting Conflict: Change The Cells Font Color And Bolding As Long As The Value Is Within That Date Range
I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:

Formula is = MOD(ROW(),2)

My Second Condition is

Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.

View Replies!   View Related
Change Cell Background Color Based On Multiple Conditions
My Excel skills are basic at best, with no knowledge of VBA other than finding the VB Editor. What I'm trying to do: Attached is the file I'm working with, to give you a better idea. When a name is selected in column B, the cells in the non corresponding columns E to N should have a black background color. For example, when you select Mike in column B, the cells in that row in columns I to N, named Dave and Dean, should become black. If you select the name Dave, columns E to H (Mike) and M to N (Dean) should become black. In addition, the background color of the non-black cells should change depending on the status.

OK : green
FAIL: red
N/A: orange
Exceptions: blue

The attached file shows a few possible results, manually entered.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved