Case Statement On Conditional Formatting
Dec 28, 2007
Set MyRange = Range("A1:AZ9615") ' Range to apply format to
For Each Item In MyRange
Select Case Item.Value
Case "1780", "1800", "1810", "2050", "6170"
Item.Font.ColorIndex = 3
'x = 3
Case Else
x = xlNone
End Select
Item.Rows.Interior.ColorIndex = x
Next Item
I want it to search that range and turn those Numbers in Quotes to Red, which it works fine, but Somtimes those numbers are Imbedded in a string excample "1810-1-DAV". So my Question is When It turns my normal numbers red, how can I get it to turn the STRING RED ALSO?
View 9 Replies
ADVERTISEMENT
Apr 25, 2008
I am trying to eliminate the use of formulas for conditional formatting in my code. I heard that the CASE statement might work for this. Below is the code I have but the case does not like the second case statement.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range(("S2:V" & TTRows), ("W2:W" & TTRows))) Is Nothing Then
Select Case Target
Case Is >= 0.8
icolor = 3
Case is >= 0.7 and not >= 0.8
icolor = 6
Case Else
icolor = 0
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
View 9 Replies
View Related
Aug 12, 2007
I have recently taken on the task of creating a large protected program (without macros) that automatically updates a PowerPoint presentation. I created corresponding workbooks for each sheet in the original (master) workbook. The corresponding workbooks are then used to paste object into the power point presentation. The only problem is it will not update format (text color) changes into the corresponding workbook. I have solved half the problem with conditional formatting, but I want to conditionally format the color of text to two different colors blue if Aa and Red if all CAPS. how to differentiate between Standard word and All CAPS in the conditional formatting.
View 7 Replies
View Related
Feb 25, 2008
I need a macro that will format a a column based on content that is case sensitive. Example, a cell that contains a capital 'P' will be shaded gray with red text where a cell that contains a lower case 'p' will not change format.
View 3 Replies
View Related
May 5, 2009
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
View 5 Replies
View Related
Jul 30, 2014
I wish to use a formula to highlight a date based on two criteria in two separate columns;
The first is the Date that will be in Column B.
I want it to highlight the date if : Today() is greater than that date in the cell plus 5 years (or 1824 days)
The second criteria I want is for the date to be only highlighted if the corresponding cell (in column A) does NOT contain the term ("Basic")
I have the formula:
=AND((TODAY()>(B1+1824)),A1=("Basic"))
which highlights the date correctly IF the term in Column A is "Basic" : But I cannot figure out how to insert the NOT statement to make the formula flip around so it only highlights those dates older than the 5 years requirement and NOT contain the "Basic" in column A.
View 2 Replies
View Related
Mar 30, 2009
I have several rows with data in and what I would like would be to total the amount of time "CU" is displayed and if that number is less than say 10 the formatting is applied. I need if possible to be able to do this via the one condition.
View 2 Replies
View Related
Oct 14, 2011
I type this formula and it work only for the first and the last condition, for the other condition it return to me FALSE. i dont now what is wrong,
=IF($C$10=1200,$E$10
View 1 Replies
View Related
Apr 11, 2014
Can you do a IF statement in Conditional Formatting? I need a column to highlight Red if there is a blank in it based on the current time COMPARED to the stated time in another column (on a separate tab)
So if current time is 1:30 PM and the stated (appointment) time on the other sheet is 1:15 PM, and my control field is still blank, I need it to turn Red.
View 1 Replies
View Related
Mar 24, 2014
Conditional formatting. I have been tasked with creating a simple game for a group training project.
The game consists of users filling in the answers to questions (simple one word text answers). My boss wants to have a master sheet with the correct answers, and a subsequent sheet that will then compare the employee's answer to the master sheet. If the employee's answer is correct, then the output sheet simply displays the correct answer, however, if the employee's answer is wrong, he would like the output to display the correct answer and highlight the cell.
Is there a way to have the output sheet highlight a cell only when the answer on the employee sheet is different from the master sheet? I was thinking along the lines of using an if statement to see if the values of the employee sheet differ from the master sheet, but I do not know how I would then use that result to highlight the cells in the output sheet.
View 1 Replies
View Related
Mar 25, 2014
I created added conditional formatting to the Response Due column, to keep track of when response was due.
I only want the conditional formatting to be applied if the cell next to it is blank.
For example, the last one on my attachment is due 3/26/14 but I have already submitted a response (see column K)
What can I add so that only those dates next to a blank in column K get the conditional formatting?
Sample.jpg
View 2 Replies
View Related
Oct 19, 2005
I have several worksheets in a workbook that has dates of completion of
various training topics. Some of these topics require annual (12 months),
bi-annual (24 month), 6-month, or even 2-month re-training.
What I am trying to accomplish is to automatically shade all cells for dates
that fall within one month of these periodicities based on the computer's
clock when a workbook is opened. Unfortunately different columns have
different periodicities so what I was planning on doing was putting that
monthly number in a cell ("12" in A1000, for example for all Column A
values). When the file is opened, if any of the dates in column A are more
than 11 months from today's date (A1000 minus 1), they are within one month
of needing to be re-trained and I want the cell shading to be a light
yellow.
View 11 Replies
View Related
Dec 11, 2013
I am attempting to add conditional formatting (yellow fill) to cells that are greater than 15% or less than -15%. I've tried the following formula but, it highlights all cells.
=or(b2:b5>15%,b2:b5
View 1 Replies
View Related
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
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
Oct 26, 2009
I wrote this short program that parses out last name and zip code from one cell into two cells so I can map the sales data. I flagged each sale as either member, non-member, or neither, based on the prices of the items (members get 25% off). I used the case statement below.
the problem is that for one of the sale items it's getting flagged as "neither" despite being at the members price level. it works for that same item in other records. the quantity at error point is 3 for two of the errors and 6 for the third, but the strange thing is it works for a few other sale records in which the person also bought 3 of that item (the quantity is not the culprit, or sole culprit).
Here's the code. Obviously Price and Quant are the two variables involved. I declared Price as a double and Quant as an integer, even though both have two decimal places in the data file. That way the product of Quant and each items price for one and Price will both have only two decimal places even though it was rounding off that product to two decimal points without declaring either variable. I cut out some of the case statement so as not to exceed the character limit.
View 4 Replies
View Related
Feb 21, 2007
I have a form with seven check boxes on it. The code that I have been working on is below. Using the select case works exactly as I want it to unless someone checks the combination of boxes that returns a "True" for both "Apples" and "Oranges". If that combination is selected I cannot get the select case to return the correct form. I was trying stay with the select case code but I am not sure that this is the correct approach for what I am trying to accomplish.
Private Sub CommandButton1_Click()
If Form1.CheckBox1.Value = True Then X = "Apple"
If Form1.CheckBox2.Value = True Then X = "Apple"
If Form1.CheckBox3.Value = True Then X = "Apple"
If Form1.CheckBox4.Value = True Then X = "Orange"
If Form1.CheckBox5.Value Then X = "Orange"
If Form1.CheckBox6.Value = True Then X = "Orange"
If Form1.CheckBox7.Value = True Then X = "Orange"
Select Case X..........
View 2 Replies
View Related
Nov 8, 2007
I'm trying to put the following into a workable VB code. I don't know whether it is best to use Select Case or If Then statements or whether either can perform the tasks.
Case 1 - Range(N1).Value = KG Then Range(M1).Value = Range(V1).Value
Case 2 - Range(N1).Value = M2 Then Range(M1).Value = Range(V1).Value
Case 3 - Range(N1).Value = NO Then Range(M1).Value = Range(U1).Value
View 9 Replies
View Related
Apr 6, 2008
i have followed the following criteria correctly
Create a function GetProducerRow() which takes in one String input (give it any name following the conventions) and returns an Integer.
Write a Select Case statement inside the function.It should compare the input string with the various companies listed in the Voting tables in cells F5:F9.
The Case should not use hard coded values for the names of the companies but something like Range("F5").Value.
In each case, set the return value by assigning the appropriate row number. For example, the value assigned would be 5 for "The Hershey Company" store in F5.
Remember to write Case statement for each of the 5 producers.
Add the Case Else in-case none of the producers match the input and have the return value set to 0.
Function GetProducerRow(strProducer As String) As Integer
strProducer = Range("F5")
Select Case strProducer
View 9 Replies
View Related
Jun 16, 2009
I made this case statment below to look at a cell in Q and if the cell value is "Large Area" then the cell in P same row should be 1 if the case is Varsity then it would be 2 however I keep getting a Type Mismatch. I tried Picker.Text and Picker.Value as well I didn't get errors but it didn't work either.
View 5 Replies
View Related
Sep 9, 2012
I am attempting to use the following code to move certain sheets to specific locations in my workbook. The case statement containing the InStr function isn't working eventhough the "Data" tab does exist and the InStr function does return 1.
Code:
ThisSheetToMove = Sheets(SCount).Name
Select Case ThisSheetToMove
Case "Schedule"
Sheets("Schedule").Move Before:=Sheets(1)
Case InStr(1, Trim(ThisSheetToMove), "Data") > 0
Sheets(ThisSheetToMove).Move After:=Sheets(SShtLast)
End Select
View 4 Replies
View Related
Nov 19, 2013
I want to use wildcards and it seems I cannot do it with case select, is it possible?
Its required because for the list of PSV's I have more than 2000 names and I would like to use *PSV* for them all instead of typing them all.
Here is the code below:
Option Explicit
Sub Colorize(Rng As Range)
Dim Cel As Range
Application.DisplayAlerts = False
Application.EnableEvents = False
[Code] .......
View 4 Replies
View Related
Aug 27, 2013
I've selected a case statement (see below) but I want to that code to work automatically. So i do not want to press a button to show a certain value in S2. But I want cell c2 to automatically pop up the value (depending of the value in V4). So kind like an if/then statement.
Sub CASEMEDEWERKER()
Select Case Range("F4").Value
Case "Medewerker"
Range("S2") = "M"
Case "Interview"
Range("S2") = "I"
Case "Data"
Range("S2") = "D"
Case "Observatie"
Range("S2") = "O"
End Select
End Sub
View 4 Replies
View Related
Sep 25, 2009
I have the following Case statement that changes all the cells to the correct color, but does not change the Font color as specified in the Case. All the text is White (fcolor = 2) instead of just the 3 types of cells specified.
View 2 Replies
View Related
Apr 25, 2013
I'm having problems implementing a Select Case statement. Keep getting case without select case at Case condition2.
Code:
Select Case Condition
Case condition1
If Cells(rsRow, rsCol).Value = "" Then
Cells(rsRow, rsCol) = TextBox2.Value
[Code] .......
View 3 Replies
View Related
Jan 14, 2007
I have a workbook with 2 sheets I want to make an autofilter by two method :
- select case statement
- two dates
View 4 Replies
View Related
Jan 31, 2010
I would like to have a nested if or select case statement to handle a worksheet event.
The conditions it will check are:
1.Make sure target is w/in range, otherwise EXIT
2.Make sure that target offset value is not empty, otherwise display message
3.All is good, open form
Ive tried various formulations and positionings of the statements ,but not all conditions are met with equal success.
What happens is I get the the first two conditions, but the third doesnt work.
View 6 Replies
View Related
Dec 11, 2008
I have 5 formatting conditions that I need to add in my macro and have just recently found out that I can't have more than 3 in my version of Excel - DOH.
Anyway, some research has indicated that you can use something called select case to get round this?
I need to colour the range as follows:
View 14 Replies
View Related
Feb 9, 2010
I am adding shapes with text (msoShapeOval) to an image on a worksheet but I cant get the size of the "ShapeRange" to work dependant on the value assigned to "sTxtlen".
I had msgbox's within each Case but none were triggered allthough using another msgbox to show the length of sTxtLen is correct prior to the Select Case.
View 9 Replies
View Related
Mar 13, 2014
I have a worksheet which is populated from a macro using the following code.
Code:
Sheets("Create Sub Contractor").Range("B6:B65").Copy
With Sheets("Sub Contractor Information").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.PasteSpecial (xlValues), Transpose:=True
End With
The problem is that some of what is being copied needs to be Proper and Some Upper, therefore I cant use a paste special option.
What I'd like to be able to do is format the columns in the destination sheet ("Sub Contractor Information") from row 4 down to what ever format they need to be individually as there are some columns that are numbers, some text and numbers........
View 4 Replies
View Related
