Vba help - IF cell has colour & contains text then ...

Fra88

New Member
Joined
Jan 17, 2017
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I am struggling with a code line for a simple macro.
Thank you in advance for taking some of your time and to anyone who will help me :)

I have 2 sheets ("data" and "macro").
Sheet "data" contains details of users (column A contains data of user 1, column B of user 2 etc) these data have different colours (yellow, green, etc corresponding to different prices).
I am asking the user of the macro to write down in cell "B5" of sheet "macro" the user that he is looking for.

The macro copies the data of that user and (copy from "data" into "macro sheet") - this part works.
Then the code should calculate the due amount applying two "if" conditions.
If the data in column "D" has colour "green" and "has text/value", then copy the value from a fixed cell (D27) into the corresponding G cell.

In the below code I was able to build the macro working based on the colour.
But I am not able to add the condition "only if D cell contains text or value".
The idea is that the value of D27 should be added only if both conditions are met

Thank you very much for any support or suggestion!

VBA Code:
Sheets("data").Activate

 If Sheets("macro").Range("B5").Value = "Imrich" Then
        Sheets("macro").Range("F3:F20").Value = Sheets("data").Range("D3:D20").Value
        Sheets("macro").Activate
    End If

Dim LR As Long, I As Long
Sheets("data").Activate
LR = Range("D" & Rows.Count).End(xlUp).Row

For I = 1 To LR
    If Sheets("data").Range("D" & I).Interior.Color = RGB(255, 255, 0) Then
        Sheets("macro").Range("G" & I).Value = Sheets("data").Range("$D$27").Value
    ElseIf Sheets("data").Range("D" & I).Interior.Color = RGB(255, 255, 0) Then
        Sheets("macro").Range("G" & I).Value = Sheets("data").Range("$D$26").Value
        Sheets("macro").Activate
End If
Next I

        Sheets("macro").Activate
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
Just looking quickly, it appear you should add an And test to your If..Then test where you check if the length of the cell's value is greater than 0.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top