Set Cell Fill Color Based On Conditions In Two Cells?

Nov 22, 2012

I want to highlight the cell of Column 'A' of excel only when it does not contain the text like "Verify", "Validate" or "Evaluate" in its content if, the value in corresponding cell of Column 'B' holds the value 'Y'. Secondly, the column 'A' can't contains the words like 'Verify', 'Validate' and 'Evaluate' if the corresponding cell in Column 'B' holds value 'N'. So just need to highlight those discrepancies if its there.

Column A
Column B
Expected Action

Press F3
N
Its Fine

Verify this..
Y
Its Fine

this....
Y
Need to Highlight Cell of Column A as Value in Column B is Y but the column A does not contain any value like "Verify", Validate" or "Evaluate"

Verify This.
N
Need to Highlight Cell of Column B as the Value of Column A contains words like "verify", "validate", "evaluate" but corresponding cell value in column B does not hold value "Y".

View 3 Replies


ADVERTISEMENT

Fill Cells With Some Specific Texts Based On Multiple Conditions

Jun 18, 2014

I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.

Code to fill these blank cells automatically with some specific texts based on the attached conditions.

I have attached the workbook for more details. FillCellsTest.xlsm‎

View 10 Replies View Related

Color Cells Based On Entry. More Than 3 Conditions

Nov 21, 2006

I was hoping there was an alternative to having to use so many named ranges for each sublist (there would many sublists in the actual workbook). Anyway, I'm a little uncertain about the code you posted - do the LIST and SUBLIST codes go in Source in Data Validation, or in Source in Dependent Validation Lists Based On Header Values? Can you give me a bit more instruction on this? Also, I'm trying to use the following code to color cells upon a change in value in V1 or V2, but can't get it to work. Can you kindly tell me what's wrong with it?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "V1" Or "V2" Then Exit Sub
Select Case [V1].Value
Case "A"
Target.Interior.ColorIndex = 40
Case "B"
Target.Interior.ColorIndex = 35
Case "C"
Target.Interior.ColorIndex = 36
Case "D"
Target.Interior.ColorIndex = 34
Case "E"
Target.Interior.ColorIndex = 19
Case "F"
Target.Interior.ColorIndex = 24
End Select
End Sub

View 7 Replies View Related

Color Cells Within Matrix Based On Conditions Outside

Jun 5, 2008

In 'MAIN REPORT' there are thousands of columns of data, of which the important columns are 'Factory No.' and 'Row No' and 'Quantity'.

Each factory has its own worksheet as well.

In each of those factory sheets there is a 15x65 grid for each of the 65 rows that are mentioned in MAIN REPORT.

I have created and IF statement that checks if there is any data in 'Quantity row' and if there is it enters a "1" in the that cell of the virtual Factory grid.

This is the formula:
=IF('MAIN REPORT'!I2="","",1)
However, this simply checks that data while there is only 1 quantity for each row of each house.

Sometimes there will be different quantities (for diff. products), and I need a formula/macro that checks the line no. column and then,

If there are 5 items in row 7 for example then it will place five "1"'s in the first five cells of row seven in the corresponding factory sheet.

View 3 Replies View Related

Color Cells Based On Multiple Conditions Of Different Cells

Jan 15, 2008

Im trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. Ive searched and found similar forums here, but since Im ignorant in VBA code, I couldnt manage to make adjustments. So if you can help me with the code, I would really appreciate it!

Condition and Results required would be:

IF column C = S than on Column B = color cell light blue with white border
IF column C = P than on Column B = color cell Green with white border
IF column C = A than on Column B = color cell Yellow with white border
IF column C = L than on Column B = color cell Red with white border
IF column C = C than on Column B = color cell Dark Blue with white border
IF column C = than on Column B = color cell white
IF column C = V and has white background than on Column B = color cell white
IF column C = V and has a Dark Grey background than on Column B = color cell Dark Grey

I dont know if the last two are feasible.

View 6 Replies View Related

Copy Cells Based On Fill Color

Sep 9, 2006

i have some problem in display the highlight cell to another worksheet. Below is in module, i would like to display the highlighter cell to another worksheet.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/2/2006 by user
'

'
Range("I351").Select
ActiveWindow.SmallScroll Down:=-12
Range("I349").Select
ActiveWindow.ScrollRow = 1
Range("E1:E400").Select

View 9 Replies View Related

Color Fill Cells Based On Value Being Between 2 Values

Feb 22, 2008

create formula/condition to colour fill different cells based on multiple Criteria.

View 3 Replies View Related

Delete Cells Based On Fill Color

Mar 11, 2008

how to sum/count cells based on background color [url]

how to delete cells based on background color?

View 3 Replies View Related

Fill Cells Color In Worbook Based On Other Cells

Jan 27, 2005

how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)

View 9 Replies View Related

Fill Range With Data Meeting Conditions Based On Cell Value

Apr 23, 2009

refer to attached worksheet.

I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.

View 9 Replies View Related

Get Conditional Formatting To Change The Cell Color Based On A Few Conditions?

Jul 19, 2013

I'm trying to get conditional formatting to change the cell color based on a few conditions.

I want the cells in column D to turn red if;

- The number in column B has a match in column A

- And the corresponding cell in column C is not blank

- And the corresponding cell in column D is blank

In the example below, cell D4 should be red

If there's a match (Column B to Column A) and;

- If cell in column C is not blank and cell in column D is not blank, do nothing.

If there's a match (Column B to Column A) and:

- If cell in column C is blank and cell in column D is blank, do nothing.

Here's the formula I attempted but didn't get it to work.

IF(AND(C2"",D2=""),MATCH(B2,$A$2:$A$15,0),IF((AND(C2"",D"")),"",""))

View 3 Replies View Related

Change Cell Background Color Based On Multiple Conditions

Aug 6, 2008

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 7 Replies View Related

Compare Cells For Conditions & Color Corresponding Cell

Sep 26, 2007

I am trying to add a third if statement to my existing code looking to see if a cell in a column is blank and the corresponding cell in another column contains a number that is greater than or equal to 1. I can't figure out how to write that part of my IF statement.

If Cells(r, 4) = " " And Cells(r, 3) >= 1 Then
Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 35
Set rng = Union(Cells(r, 1), Cells(r, 3), Cells(r, 4))
rng.Copy
Worksheets("Sheet1").Range("G40:I40").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Cells(r, 5) = "NEW"
End If
Next r

View 4 Replies View Related

Change The Fill Color Of A Cell Based On Its Value

Jan 20, 2009

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 9 Replies View Related

Formatting Cell To Fill Color Based On 2 Of 3 Values?

Jul 23, 2013

I'm trying to discover the best way to accomplish formatting a cell by filling it a certain color based on 2 of 3 criteria of other cells.

Column A has the title of the field. Cells B1:D1 have one of 3 values: Nothing/blank, Complete, or x. If all of the x's are gone within the range of B1:D1, I'd like to automatically format cell A1 to fill in Blue (or any color).

A1__________________B1_________________C1_____________D1
Generic Name______Complete_______________x_____________

If the 'x' in C1 is changed to the value of 'Complete', I'd like A1/Generic Name to be filled in with a color.

View 8 Replies View Related

Delete Columns And Rows Based On Cell Fill Color

Jan 31, 2014

I am a locksmith and attempting to write a spreadsheet app. for my line of work. The output page will use columns A-G and rows 1-?10000? depending on the size of the system. If a 5 pin system is requested then columns A+B will be blacked out. 6 pin system A is blacked out. 7 pin system nothing blacked out. Also, if any cell matches the master key that was input, that cell is filled with red. I know, confusing.

Basically, I am trying to remove the blacked out columns automatically, and to remove the enitire row is any cell in that row is filled with red. Then to remove blank lines.

View 2 Replies View Related

Changing Fill Color Of Autoshapes Based On Cell Numeric Values

Jun 10, 2006

how I can change the color of an autoshape based on a value in a cell?

View 4 Replies View Related

Cell Color Fill Depending On Condition Of 2 Other Cells

Jun 23, 2009

Afternoon everyone i am having abit of trouble working on an excel 2007 spreadsheet. In cell I1 i have a tab called Color. i want the cells below I1 to be filled with one of 3 colors green, yellow, or red depending on whats in cell F "Status" (closed or open - in progress) and cell G ECD for estimated completion date.

Green - i need it to fill green if status is closed. Yellow - need it to fill yellow if ECD is any date greater than today and if status is open. Red - need it to fill red if ECD is todays date or older and status is open.

View 3 Replies View Related

Select Row Based On Cell Criteria Remove All Formulas From Row And Change Fill Color

Apr 23, 2014

I have a worksheet that when a row changes based on the value of column B, I want to remove all of the formulas found in the row but keep the existing values, and then change the color of the row.

In the sample file attached, when the value is "Closed", that row will keep the existing values and then it gets grayed out. Rows that are still marked "Open" need to retain the formulas in case other information changes.

I have tried copy/paste special using autofiltering but that doesn't work because of the hidden lines. This file changes on a daily basis and I need a quick way to update the file.

SampleFile.xlsx

View 1 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

Add A Textbox At The Current Position (selected Cell) With A Set Size, Fill Color, And Border Color

Nov 2, 2008

I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:

View 2 Replies View Related

Count Cells Based On Conditions In That And Other Cell(s)?

Jan 28, 2013

I have an issue log containing (separate) columns with dates, numbers and text.

One column (A) has the status (open/in progress/pending/closed/re-opened)
One column (B) has raise dates
One column (C) has close dates
One column (D) has the type of track (this is text, 3 types)

I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).

After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.

So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".

After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.

View 3 Replies View Related

Auto Fill Out Of Cells With Multiple Conditions

Sep 8, 2013

I have this excel file with 5 columns on it and one of the columns is dependent on the four columns. I just want to automatically fill out the 5th column (Completed, In-progress and not yet started) based on the conditions set on the four columns. Although, the four columns have many conditions, it has been categorized with 3 colors. If all of the 4 columns are GREEN, 5th column will show Completed, If all of it are orange- Not yet started and if one of the columns had different color 5th column will reflect -In progress.

View 1 Replies View Related

Change Font Color Based On Conditions

Jan 27, 2007

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 6 Replies View Related

Color Cells By More Than 3 Conditions

Sep 13, 2007

I am getting a "Run-time error:'13' Type Mismatch" when we try to paste a value (in this case "Y" or "N") in multiple cells at once. I did look up this error on the microsoft site and found this. http://support.microsoft.com/kb/821292. which didn't seem to be the exact issue I was having because I am not using a macro.

Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Call ChangeRowColor
If Not Intersect(Target, Range("$A:$E")) Is Nothing Then
Select Case Target
Case "y", "Y"
icolor = 4
Case "n", "N"
icolor = 3
Case "?"
icolor = 6
Case "n/a", "N/A"
icolor = 13................

View 2 Replies View Related

Fill Multi Columned ListBox Based On Range Meeting 2 Conditions

Mar 14, 2008

I'm trying to populate a dropdown Combo Box by using conditions.

In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.

I've been trying to use worksheetfuntion.Index.

View 4 Replies View Related

Populate A Cell Based On Conditions Of Other Cells W/out Putting A Formula In The Cell

Oct 10, 2009

Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?

View 14 Replies View Related

Fill Cells Based On Another Cell Value

May 1, 2009

refer to attached worksheet. If value in D7 is less than zero; then D8 = 0%
If D7 > 0, then D8 = value in C8, and this value must also be copied into 9 adjacent cells in row 8 from D8 onwards.

View 4 Replies View Related

Colour Cell Based On Few Conditions / Cell Values Of Other Cells In Same Row?

Jun 28, 2014

In the xls for each step I have 2 raws-planned and actual. Step planned duration is populated manually over the weeks.Before that row we have another reflecting the actual step status per week

I would like to find a way how cell reflecting the actual status of a step can be automatically populated (coulored) based on the colour/value of activities that are planned for that week and for that step.Activities are listed below the step and again have planned and actual row.

The rule should be : if for a week we have several activities all of them should be finished in order to have step stataus auto populated as green. If a single activity planned for that week is not done-then weekly step status should be red.

The activities for each step are grouped below the step. It seems that one of the difficult part in that request would be how formula will understand where starts and finishes the activities that belong to one step. To get that happen I placed a column showing step and another column where we activity.

View 9 Replies View Related

Fill In Cells Based On Other Cell Values

Dec 21, 2009

I am looking for a way for Excel to fill in cells in a column that has data sporadically in it. I am importing a text file into excel and saving it as an Excel file.

In the example attached, I need the numbers filled in all the way down to the next number. I don't care about the text, just the numbers. I realize I can copy and paste, but these files can be several hundred rows long and it seems there should be a better way.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved