I am trying to figure out how would be the best way in VBA to identify if a cell is colored green (column C) then in column L I want to say "YES" else nothing.
I have came up with a count so I know how many records to look at but have nothing from there.
Public Sub KPI()
Dim cls As Object
Dim i As Integer
Dim rng As Range
Set rng = Range("C9:C100")
For Each cls In rng
If cls.Value "" Then
i = i + 1
End If
Next
End Sub
I have large data sheets with blocks of blue-colored cells. I want to surround them with lavender-colored cells. Then I want to run a command to Clear Contents of all lavender-colored cells. Is this possible?
So first I have: Black-text blue-text blue blue Black-text blue-text blue blue Black-text
Then I want: Lavender Blue Blue Blue Lavender Blue Blue.................
I am attempting to create a ROI calculator for my place of work. I want a blank cell to fill in green or red based a cell below it being equal or greater than 5.0%. What is the Macro or equation to do that?
I've set a conditional format to flag the row green if cell e2 has a Y or a N in the cell.
Currently i have the following conditional format formula is as
=IF($E$2="y",TRUE,FALSE) this is set to change row to green =IF($E$2="n",TRUE,FALSE) this is set to change row to red
My Questions =
What i want to do is copy this formula through the 500 or so rows and have the formula adust to =IF($E$3="y",TRUE,FALSE) For row 3 =IF($E$4="y",TRUE,FALSE) for row 4 =IF($E$5="y",TRUE,FALSE) for row 5
Ect.
I've tried using the formate painter but all it does is set all row to conditionally format dependant on what is in Cell E2. Thoughts on how to copy this formula is conditional format so i don't have to manually adjust it line by line. Doing this 500 times over would be a pain.
I have five columns D3 to H3 all with different amounts/currency value. I'm looking to get a formula or use conditional formatting to scan those five cells and highlight one of them green when Excel has determined which one is the cheapest out of the five.
In a column in my spreadsheet i have to input a number; either: 51-61 and next to it is either m or o. so in Column A row 1 : 51m, Column A row 2 60m, etc Example:
51m 51o 55o 60m
I want to conditionally format this such that for 51 it turns the cell background green, for 52 red, 53 orange,etc. with a number having a specific color associated with it. 03' does not have a text choice.
I need a UDF to test a single cell if it is colored. If it is the result returned should be a 1, if not the result will be blank. This is what I have so far, but I don't know much about VBA.
Function Filled(MyCell As Range) If MyCell.Interior.ColorIndex > 0 Then Result = 1 Else: MyCell = "" End If End Function
I have used a formated find (Ctrl+F) to color all cells that contain a certain word. I now want to copy and paste those rows (not just the cell) into a new worksheet within the same workbook. My data is in A8:F2411. The colored cell is in column B.
I have searched for previous topics on this and found the one I needed, however; it was not solved.
I'm new to 2010 and can't work out the new conditional formatting! I need to be able to enter a letter in A1 and have E1, F1 and G1 automatically show a different letter in each cell (different coloured fonts in each cell as well).
I have a range of cells (A1:A12) each with monthly sales figures.
I need to highlight where in the range the values exceed a certain amount ($50k for example). However it's only the first cell that hits the value that should be coloured, i.e. once it reaches the figure nothing afterwards is to be coloured. So it could occur in cell A3 nothing afterward should be coloured.
I've tried using conditional formatting but I can't get the cell to determine if any other cell is coloured.
I have a workbook (see attached) that has 2 sheets, Work and Holiday. I want to be able to enter a persons initials into the wrksheet 'Work' for each day of the week.
If that person is listed on the holiday sheet for that day as 'A' then the cell in 'Work' is coloured Red, if not then it is coloured Green. I have tried to get this to work using a defined list and various IF statements but all to no avail.
I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).
So I made a formula to identify the exact cell # every month.
Example:
This month the exact cell is F255 in the "Refi" sheet.
So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have
VB: ="F" & (A1)
This outputs "F255" in B1, successfully identifying the target cell.
Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)
(I cannot attach the files as it is all personal information) Basically its list that looks like this:
2014-02-11 John Doe 2014-02-12 John Doe 2014-02-17 John Doe 2014-02-10 Mary Jane 2014-02-11 Mary Jane 2014-02-16 Mary Jane 2014-02-10 Frank Simms 2014-02-11 Frank Simms 2014-02-18 Frank Simms 2014-02-11 Tony Sly 2014-02-12 Tony Sly 2014-02-17 Tony Sly
Im trying to identify anyone who does not have an entry on 2014-02-17. I've played around with combing IF and VLOOKUP and Conditional formatting but I couldn't seem to get it to work. If it were to find a date, no problem, but to find someone without that date I'm having issues. If there was a way to highlight or identify Mary Jane and Frank Simms since they don't have an entry on 2014-02-17, that would be great.
I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted.
I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank.
What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added.
In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].
I need to identify rows where the contents of a given cell are equivalent to the cell below. I have managed to get this working based on iterating through the cells and selecting them both for cutting.
However it doesn't work effectively: It seems to be leaving blank lines in the original sheet and further fails after the first execution. I would be grateful for any suggestions on a good method to perform the following operations: (1)to compare the cell with the cell below I have used
If cell = cell.Offset(1, 0) Then
(2)to act on the rows for the two connected rows
Sub Test4()
' Loop through rows
Dim TheExtract As String Dim cell As Range
'Application. ScreenUpdating = False For Each cell In Worksheets("Sheet 1"). _ Range("A1", Worksheets("Sheet 1").Range("A65536").End(xlUp)) TheExtract = cell If cell = cell.Offset(1, 0) Then cell.Rows("1:2").EntireRow.Select Selection.Cut Sheets("Test").Select ActiveCell.Offset(3, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown End If Next cell 'Application.ScreenUpdating = True
I have an embedded chart on my worksheet.I can select a cell behind the chart using the keyboard arrow keys.Is there a way of doing this using a mouse click,so that I know which cell i am pointing to/choosing?
can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.
1) If “Completed" , then highlight as green 2) If “Pending” , then highlight as red 3) If “In Progress” , then highlight as blue 4) If “X Required” , then highlight as green
I need to include in the third conditional formatting box that if the cell populates "Completed" or "X Required", then highlight as green. Is there a formula I can use in the conditional formatting box ? I am using Excel 2000.
I am trying to loop through all the worksheets in active workbook. But I want to ignore all those sheets which does not have a single formula containing cell. I mean if the sheet has at least one cell with a formula then the loop should work on that sheet else ignore that sheet.
