I am working on a code in which i have to change the color of certain cells if the value of a cell is Y/N. The color should change with the user input.
Set wsheet = wbook.Worksheets("XYZ")
For Row_start=10 to Row_count 'Row count is the last used row in the worksheet
RowValue = ActiveCell.Row
colValue = ActiveCell.Column
if colValue = 17
[Code] ....
I have a large workbook with some tasks.
My problem is:
I have 8 columns with info.
In the second column i type a part number and in the third one i type "Yes" or "No".
I wanted to make the second columns cell turn red if the third column's value is Yes or white if the value is No.
Is possible to do that with the help of a formula, conditional formating or vba?
I prefer to use a formula or the conditional formating.
I searched the net but i found a solution if the third column contains a number not a text.
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 2 Replies View RelatedI'd also like to have it be able to change the 2 cells to the right, in columns (M&N) whenever the conditional value given below are true. I thought I had posted this message yesterday but never saw it show up as a thread.
Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("L1:L800")
For Each Cell In MyPlage
If Cell.Value = "Hold" Then
Cell.Interior.ColorIndex = 3
End If
If Cell.Value = "Complete" Then
Cell.Interior.ColorIndex = 4
End If
If Cell.Value = "Issued" Then
Cell.Interior.ColorIndex = 43
End If
If Cell.Value = "Release" Then
Cell.Interior.ColorIndex = 36................
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.
I am trying to create a formula that will change the cell color in a range of cells in the row. I want the color to change based on the information in a particular cell appearing on a different sheet. What sheet the information is on determines the color the cells change to.
Example: I have 4 sheets I am working with. We will call them A; B; C; and D. I would like the color of cells A5-I5 on sheet D to change to red when the information in cell A5 from Sheet D shows up in any cell in column A on sheet A. If the information from cell A5 sheet D appears in any cell in column A from Sheet B then the color will be yellow, etc.
i want the user to be able to change the cell color on the click of a command button. the worksheet is protected. when the user click the command button the active cell changes to red and offsets by 1. then the work sheet is locked again.
The two problems I am having is 1. I want the range to begin from row 10, column k to column FD. all cells down
the second problem is the current code allows me to edit locked cell columns A to J ...
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 7 Replies View RelatedI 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 3 Replies View RelatedI 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 4 Replies View RelatedI 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?
I have a drop down sub pasted to worksheet:
Private Sub ComboBox1_Change()
ComboBox1.List = Array(100, 200, 300, 400)
If Range("I11").Value < Range("N11").Value Then
If Sheets("Profile").Range("K18").Value < ComboBox1.Value Then
Range("I11").Interior.ColorIndex = 2
Else
Range("I11").Interior.ColorIndex = 3
End If
End If
End Sub
I want it to change the cell color on drop down change. How can I modify things to have the change in drop down selection?
when I execute this code on the selected cells, it completely ignores my selection, and makes the entire worksheet black.
Sub ColorCells()
Dim cell As Range
For Each cell In Selection
If cell.Value = "U" Then
cells.Interior.Color = 3
I am trying to change the color in Range("A1:bb5000") for each worksheet unless it is protected.
Here is my code
Sub Color_Sheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1:bb5000").Select
If Item.Locked = False Then
With Selection.Interior
I have been given a spreadsheet that turns whole rows different colours when certain data is entered into a cell. I want to locate the code and use it elsewhere, but cant find it?
I have looked at all of the change related procedures in the drop downs, for the Workbook e.g.
Workbook_SheetChange
but no matter where I cant seem to find any code at all.
Is there a way of exporting every line of code and then open this file in notepad to skim through it?
how I might find the code that is making the rows turn different colours?
I am wanting to change the background of a cell to red if the cell is blank - Basically this is to highlight in a form any missed important areas...
[URL] and how I could modify the conditional formatting/vba to return the same effect but for a selected range, not just a cell?
View 2 Replies View RelatedIs it possibe to change another cells color (Fill) based on what is placed in that cell? Example: A1 =if there is an "x" in A2 highlight A1 Green, but I have information in A1 also. Or if I had a different cell say= if there is an X in A1 Highlight A1 Green ect.. This key is to highlight a cell that I have information in based on a different cell having an X in it. If ther eis no X in the cell I have indicated, nothing needs to happen.
View 2 Replies View RelatedWhen the sum of B7-D7 is less than cell (I7) I would like to change the shade of B7-D7 to orange to indicate that larger values need to be entered to equal the value of cell I7. And when the sum of these three cells does equal I7 their color should change to green. I recorded two macros to change the colors and I've run them to verify they work. But I've got something off in my simple macro below.
[Code] .....
Macro is not working .So the macro I am using is supposed to look back at all the months tab, match the Name from previous tabs and if they match change background color of the active sheet's cell to red.
Right now the macro is only able to lookup the name in the previous tab (i.e. For December it will only lookup November tab and not October-January).
e.g. (Candidate name is Jack King and he is in (october's tab, Column A Cell 2 )
I want this candidate name to turn red once I type his name in November/December tab.
I have pasted the code I am using below:
[Code] ......
I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.
The code works, but it really slows my worksheet down when opening. Is there better way to write this?
Code:
Dim myRange As Range
Dim cell As Range
Set myRange = Range("V6:V50000")
For Each cell In myRange
If cell.Value < 2 Then cell.Font.ColorIndex = 5
If cell.Value < 1 Then cell.Font.ColorIndex = 3
Next
Is there a way to combine cells in excel and chage the color of part of it?
Example: On my sheet I have multiple cells I need to combine:
Say A1 reads 23.65
Say A2 reads 43.65
Now on A3 I want it to read "Estimated budget 23.65 vs 43.65"
I can get this to work but here is the problem, I want the 23.65 to always be red and bolded and the 43.65 to always be blue and bolded.
I have column "C", starting from "C5" with dates till C200, but not all cells have dates some of them are also blank.
I want the cell to change color depending on the date.
If the written due date is in a month from today, I want it to turn yellow, and if the due date is in the past from today, I wanted to turn red. I want it to get updated every month.
how to clear contents and change colour index of cells.
This is for a booking system. The current VB codes allows bookings to be made by entering a reference number, dates of bookings and room type and bed type. When the VB codes are executed, the cells corresponding to the details entered earlier, would fill the booking table with the reference number and change the colour fill (e.g. yellow) to indicate that the slots are booked. Please refer to attached sample.
However, I do not know how to delete the booking. I would like to assign a cell for user to enter the reference number that needs to be deleted. So when the reference number is entered into that cell, user could execute a function which will clear the contents and change the colour index to none for that particular booking with that reference number(s).
Attached is a sample of the problem.
I have cells within a column that could contain both text and dates. When a date is entered - the activity is complete. I want the cell color to automatically turn green when a date is entered. Conditional formatting seems to use ranges, or today's date, or > than and < than... and I want the cell the turn green when any date is entered. I haven't been able to find either vba code or an excel formula that will work.
View 3 Replies View RelatedI got an excel sheet were in column A i have listed a few names and in column B I got their respective results (numbers) with a background color.
I want to insert a function in such a way that when their numbers exceed a certain number the background color changes from for example blue to green.
so >1200 = blue background color
1200-1350 = red
< 1350 = green
I have been using Conditional Formating to change the backgound color of the cells, this works fine. However it cannot be used with a userform text box. There I believe that the use of If Then Else statements will sort out the problem.
I have developed three conditions:
1) D10 less than E5 result True
2) D10 less than F5 result True
3) D10>E5 result False
using that result background color to turn green
Next condition
1) D10 less than E5 result False
2) D10 less than F5 result True
3) D10>E5 result True
using that result background color to turn yellow
Next condition
1) D10 less than E5 result False
2) D10 less than F5 result False
3) D10>E5 result False
using that result background color to turn red
What I cant seem to do is combine the three instances above so that the cell back color changes.
This is so easily done using the built in conditional format function but I'm have a difficulty understanding how to make this work.
I am atempting to do some autoshape conditional formating. I am about above average when it comes to working with Excel, and I know little to some Visual Basic. Here are the two things I am attempting to accomplish.
1. I am attempting to use VBA to change the color of a Large Pyramid Autoshape called "JanPyramid". The thought behind this is, I wish to have the Pyramid show vbGreen when it is <= my target (which in this case is a user inputed cell called "RECTARGET", so instead of using a number in my VB code I would like to have it refer to this cell in E58) I want the Pyramid to show vbRed when the total Recordables (this number is generated by using a COUNTIF function, so it counts all entrys in the table that have REC entered for its status, in this case the cell is called "RECCOUNT" and is located in I54) is > my target, E58 "RECTARGET"
2. I have already sort of gotten this to work by using values instead of using cells. But it is still is not perfect. I would prefer to use the actual cells because my targets and the actual counts can be different depending on what happens during that month. Here is the code I have in so far, it is with the values instead of it being the cells because I do not know how to make it read the cells instead of values:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("RECCOUNT")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value > 0 And Target.Value <= 2 Then
ActiveSheet.Shapes("JanPyramid").Fill.BackColor.RGB = vbGreen
ElseIf Target.Value > 2 Then
ActiveSheet.Shapes("JanPyramid").Fill.ForeColor.RGB = vbRed
End If
End If
End If
End Sub
This code is a hybrid from this Ozgrid thread: Changing fill color of autoshapes based on cell numeric values.
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.
I need change the color of the cell depending on the following conditions. (By the way the column will have names list)
1. single punctuations are allowed anywhere in name apart from first position.-->color should not change
2. If any name got consecutive punctuations --> color should change to RED.
3. Any punctuations apart from Dot(.), hyphen(-), apostrophe('), Space( ) are not allowed-->color should change to RED.
Example-
ShivakumaraNo Change
Shiva kumaraNo Change
Shiva'kumaraNo Change
shiva.kumaraNo Change
shiva kumarared
shiva''kumarared
shiva' kumarared
shiva. Kumarared
shiva.'kumarared
shiva:kumarared