Color Code -a Specific End Condition

May 12, 2007

I searched through the files and found a macro that works for me to color code my spreadsheets in Excle. Hwoever. How can I set it to stop when Cloumn G = the word "Created"?

Sub prettyPattern2()
Dim lRow As Range
For Each lRow In Range("A6:K500").Rows
Select Case lRow.Row Mod 2
Case Is = 1: lRow.Interior.ColorIndex = 34
Case Else: lRow.Interior.ColorIndex = 35
End Select
Next lRow
End Sub

View 8 Replies


ADVERTISEMENT

Color Table Rows Where Specific Cell In Row Matches Condition

Jul 2, 2008

I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C.

Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row.

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(1) . Interior.ColorIndex = 16

View 4 Replies View Related

VBA Code For Adding A Specific Worksheet Condition Within Subfolder Consolidation Macro

Aug 22, 2013

The issue is the I want to put the condition here in my the code that only copies the desired cells if the sheet is named specifically such as "Jan", if not named as this the worksheet should not be used. The current code I am using was posted on this site in 2009: VBA to copy specified cells from all the files in a folder

My modified code is:

Option Explicit
Public strSourceFldr As String
Public EachFile As Object
Public objFSO As Object
Public objFolder As Object
Public objFile As Object
Public strSheetName As String

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

View 1 Replies View Related

Simple Color Code Not Working On Specific Sheet

Jul 22, 2013

This very simple color code below worked before but now it gives "Application-defined or object-defined error". When I try it on a new workbook, it works fine.

Code:
Sub sdsdfsd()
Selection.Interior.Color = 65535
End Sub

View 1 Replies View Related

Difficulty Writing Code For Changing Font Color If Cell Contains Specific Text

Aug 11, 2009

I have a spreadsheet that will contain about 5-15 rows with a letter "S" in the column. If this letter S appears in the column, I need its entire row to change font color to RED and then change that row's value in column L to a negative number. is there any easy way to do this?

View 14 Replies View Related

Fill Color: Columns Would Be Blocked With Specific Color

Nov 26, 2006

1. By entering a numerical value in Cell A1 for example typing 5.
2. Then, next five columns would be blocked with specific color.
3. if i am typing 10, then 10 columns would be blocked with specific color.

View 14 Replies View Related

Color Whole Row If Condition Met In A Column

Sep 13, 2007

I have a spreadsheet and in the "I" column, it can read either "OPEN" r "CLOSED". If that reads "OPEN", I want the whole row to turn green, if "CLOSED" I want it to turn red. I have fiddled around with conditional formatting and can seem only to change that one cell!

View 4 Replies View Related

Using Three Condition For Formatting Color Of Tabs?

Aug 21, 2014

I am using the following code that I got from this forum to color tabs. But I can only get it to do two colors. Is there a way to get three conditional colors of tabs?

Private Sub Worksheet_Change(ByVal Target As Range)
With Me
Select Case .Range("D13").Value
Case Is < 20
.Tab.Color = vbGreen
Case Is > 20, Is < 30
.Tab.Color = vbBlue
Case Is > 30, Is < 100
.Tab.Color = vbRed
End Select
End With
End Sub

View 8 Replies View Related

Change Color Of Part Of Row If Condition Met

Dec 6, 2012

I'm working on a macro to change the color of the row between column W to AN if in column K the value is "FCA" (without quotes).

So far I have this, but it doesn't work:

Code:
Sub Test()
For Each cell In Range("K1", Cells(Rows.Count, "K").End(xlUp))
If InStr(1, cell, "FCA") Then
Range("W" & cell.Row & ":AN" & cell.Row).Interior.ColorIndex = 3
End If
Next cell
End Sub

View 1 Replies View Related

How To Color Excel Cells In Condition

Jan 10, 2014

I want to color cells if it is texted as ULF by green, if it is texted as OS by red in the all row down, it easy but I can't.

View 2 Replies View Related

Change Cell Color On Condition

Oct 3, 2006

I need to change automatically the color of a cell (let say A1) to green if the cell content is YES and to red if content is NO. Which function to use?

View 2 Replies View Related

Color Coding Condition Format

May 15, 2007

i'm trying to figure out a conditional formatting that if a cell is greater or equal to another cell - color a cell "GREEn"

otherwise...color the cell "red"

View 7 Replies View Related

Color Entire Row When Cell Condition Met

Sep 15, 2007

Using conditional formatting is it possible shade an entire row when a cell is = to 0. I am currently creating a spreadsheet for a small amount of inventory. I am manually editing the amount of product available.

What I want to happen is when I replace them number with the text ‘Sold Out’ I want then entire row to automatically grey that row out. Is it possible to make this happen using conditional formatting?

View 9 Replies View Related

Fill Color Of Autoshapes Condition On Cell Value?

Mar 2, 2013

fill color of autoshapes on condition depend on cell value

View 1 Replies View Related

Find Out Format Condition Cell Color?

Aug 4, 2014

I set format condition in range by tool bar(=if formula is yes then display cell.interior.colorindex=3), but I have no idea how to find out (.address) the red colour cells are displayed in range with VBA?

View 5 Replies View Related

Color Cell If First Character Meets Condition

Aug 4, 2007

I currently have a report that I have code on that will change the color of a cell based on whats in the cell.( Code posted below) However I also need to look at $J and if the first letter of the value in that column is a "z" then I would like to change that whole row to icolor 13 (purple).

For example J14 might be "Z-Thomas". if it does I would like that row 14 to change to purple.

Also I would prefer that the solution be in VB, so that we do not have to do something in VB with the fix below AND do something in Conditional Formatting.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

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

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

Change Color Of A Range Of Cells When Condition Is In Just One Cell

Sep 17, 2012

I have a conditional cell that change color or not if condition...OK so far. Now, how can I change the color of a range of cells when condition is in just one cell. Ex:

If C15>=150, change (C3;C20) to yellow.

I really can't find the way to do it.

View 2 Replies View Related

Color Fill Multiple Rows Based On Condition

Mar 22, 2007

Is there a way to color fill an entire row based on a value in a certain column? Say I have a large file and one column is "yes" or "no" If I wanted all of the "yes" rows to be colored -

View 5 Replies View Related

Color Rows For All Cells In 1 Column Meeting Condition

Oct 26, 2007

A friend is trying to change an entire row's color based on a specific cell's value in that row. He cannot use conditional formatting. This is the code he's tried, to no avail:

Sub temp()
totalrows = ActiveSheet.UsedRange.Rows.Count
For Row = totalrows To 2 Step -1
If Cells(Row, 25).Value = 4 Then
Rows(Row).Select
Selection.Font.ColorIndex = 3
End If
Next Row
End Sub

View 2 Replies View Related

Add Condition - If Value Is In Between Get Specific Text

Feb 1, 2014

I try to add some conditions to get specific text. i.e.

if cell b4 <= 20% then cell f4=01.30min
if cell b4 >= 21% and <=50% then cell f4=02.00 hr
if cell b4 >= 51% and <=70% then cell f4=02.30min
if cell b4 >= 71% then cell f4=03.00 hr

I tried different ways, including select case but unsuccessful.

View 8 Replies View Related

Find Average With Very Specific Condition

Mar 14, 2014

Let's say I had the following data ...

47
50

50
50

44
50

46
50

Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.

--If the special number is the lowest, just take the average of the four numbers, and divide it by 50
--If there is a (are) number(s) lower than the special number, then I want it to locate the smallest
---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)

So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).

View 4 Replies View Related

Average Formula For A Specific Condition

Jul 30, 2009

I have been asked at work to get some averages of data from an excel sheet. I have attached a sample workbook of the data i will be using. On Sheet1 is the data that will be looked at and on sheet2 is a sample of the results i need to achieve. I can get the data on sheet2 easy if i knew that data was static but in my case the data is going to be dynamic, so i don't know how many records each person is going to have each time if that person shows up in the record set at all. This rules out on static ranges to gather averages.

So far what i came up with is i use a macro to copy all the "Names" column to sheet 2 and then filter that data so that each name is unique, sort like a "key" for looking up values. Now i need to get averages of "Total Hours" worked in sheet 1 and display it in sheet 2 for each "Name". I have a formula worked out and almost have it where i want it but cant seem to get the range of data i need to be looking at. I need to get the Average Of hours Worked Per Person Where "Wk End Date" is greater than 1/1/2008 and the "name" matches up with the name in sheet 2.

View 3 Replies View Related

VBA Print Specific Pages Based On Condition

Jan 25, 2013

I'm looking to setup a macro that will print specific pages on the current worksheet. What pages will print in that same worksheet will be determined on the value of a specific cell.

Here is what I have that isn't working.

VB:
Sub Print_Specific_Pages()
'''''''''''''''''''''''''''''''''''''''''''''Prints only pages that contain data
''''''''''''''''''''''''''''''''''''''''''''
If Range("B3").Value = 0 Then
Worksheets.PrintOut from:=1, to:=1 'Prints page 1 when there is no data.

[Code] ......

View 5 Replies View Related

Working Out How Many People Reach Specific Condition

Dec 11, 2013

I've put together a spreadsheet look at the time taken for a vehicle to get from one building to another when called. I've set up conditional formats where the timings turn orange after 5 minutes and red after 10 minutes taken. What I also have to show though is who is taking the vehicle and break it down to show any delays to patients. Is there a formula that will automatically tell me when there has been a delay to patients?? Be amazing if there is as it would save me uber amounts of time as i have to collate a whole years worth of data!

I've attached a small sample of what I've done to show you how the spreadsheet looks.

sample.xlsx

View 5 Replies View Related

How To Copy Paste Specific Cells When Condition Met

Mar 13, 2014

I create a macro to copy cells for data starting from A4 to N4 from sheet - Register to 'Case History' when the cells in column N are 'Yes'. Also, would like to copy specific range from A-N and not the entire row. Please refer to the attachment.

Tracker Dept..xlsx

View 2 Replies View Related

Count Specific Criteria Based On Another Condition?

Jan 3, 2013

I'm trying to create a formula that tells me the following:

If the item is marked as closed (vs. active), then how many renewals/terminations replacements/etc are there? These are two difference columns in a worksheet.

View 2 Replies View Related

X Condition Lookup: Find The Specific Value Within The Table Or Array

Aug 21, 2008

Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.

View 4 Replies View Related

Detect Cell Conditional Formatting State Of Specific Condition

Nov 26, 2009

Is it possible to check the conditional state for a specific condition for a specific cell.
For example a cell has 1 or more conditions.

A condition will toggle to a condition if true or false etc.
The specifics is i use red (color 255) if a cell has not met a specific condition (of 1 or more conditions)
So if the cell is mandatory to be populated, and is blank, it will be red, if not it will be something else.

But i want to loop all visible cells with conditional formatting, and where the cell has a condition when true will show interior color = to 255 return the cell address.
To msg the user where data entry is still required etc.

View 6 Replies View Related

VBA Copy / Paste Specific Cells From A Sheet To Another Based On Condition (reference)

Nov 7, 2012

Basically, I need to copy and paste upon certain condition (references) and paste related-datas in another sheet corresponding with these references. Every references are organized by lines with indicators on columns.

In attached file, see:
- worksheet ANALYSIS:
* datas in E2, G2, D18, D19, D20, D21, D22 that needs to be copy based on reference in C2,
* then need to find this reference in worksheet called GROUP SAVE,
* and based on this reference, paste above cells value in corresponding "Indicators" column number

THEN, I will have to do pretty much the same with subgroup data. But I can figure out for this second step that request same manipulation.

View 3 Replies View Related







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